| Continue sql script: yes or no [message #533849] |
Thu, 01 December 2011 08:52  |
bibber
Messages: 38
Registered: August 2006
|
Member |
|
|
Hi,
I'm running on sql script, which I can pause using PAUSE. But wether or not to continue to execute the script, I want let the decision up to the user. More like continue: Yes / No.
It this possible?
thnx, Rob
|
|
|
|
|
|
| Re: Continue sql script: yes or no [message #533870 is a reply to message #533849] |
Thu, 01 December 2011 09:14  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
are you talking here about sqlplus PAUSE command?
If so, you may try to use ACCEPT command instead for obtaining substitution variable. However, you will have to treat its value in the code manually - I am not aware about any way for conditional code execution/termination in sqlplus. Depending on exact "script" language, you may do that decision on operating system level.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Continue sql script: yes or no [message #533938 is a reply to message #533925] |
Thu, 01 December 2011 15:06 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
What about appending a condition when user says YES
or when the user says NO?
This is of course assuming the rest of your script is SELECT, INSERT, UPDATE, DELETE commands, but alas it's a guess as you have not posted what the script does.
|
|
|
|
| Re: Continue sql script: yes or no [message #533997 is a reply to message #533938] |
Fri, 02 December 2011 01:29 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here's one option; check whether it is what you are looking for.
First, a script (its name is P.SQL) (why "P"? Why not?)
prompt Do you want to continue script execution?
accept odg prompt 'Y / N : ';
set termout off;
set verify off;
set heading off;
set pause off;
set echo off;
column a newline;
spool odg.sql
select decode(upper('&odg'), 'Y', 'prompt Executing ...',
'prompt Stopped'
),
decode(upper('&odg'), 'Y', '', 'accept return_key prompt ''Press <Return>''') a,
decode(upper('&odg'), 'Y', '', 'exit') a
from dual;
spool off;
set termout on;
@odg
select * from dept;
accept return_key prompt 'Press <Return>'
exit;
OK, let's see how it works. User is asked whether he/she wants to continue script execution and answer Y or N. If the answer is Y, script does "something" (for example, lists contents of Scott's DEPT table). If the answer is N, script exits.
First, Y:
SQL> @p
Do you want to continue script execution?
Y / N : y
Executing ...
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Press <Return>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
M:\>
Then, N:
SQL> @p
Do you want to continue script execution?
Y / N : n
Stopped
Press <Return>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
M:\> [Updated on: Fri, 02 December 2011 01:30] Report message to a moderator |
|
|
|
| Re: Continue sql script: yes or no [message #534004 is a reply to message #533997] |
Fri, 02 December 2011 02:02 |
bibber
Messages: 38
Registered: August 2006
|
Member |
|
|
Hi, since I'm using a sql scriptfile combining both sql and pl/sql, I was able to use ACCEPT.
thanks for your input anyway.
set serveroutput on
set verify off
spool &1\log\bijwerken_kaartbladindeling.log
prompt *******************************************************************************
prompt * *
prompt * Naam : bijwerken_kaartbladindeling.sql *
prompt * *
prompt * Omgeving : DIVA-Gegevensmagazijn / DIVA-Leveren *
prompt * *
prompt * Module : Schema GEGMAG *
prompt * *
prompt * --------------------------------------------------------------------------- *
prompt * *
prompt * Doel : Nieuwe bladindelingen en/of kaartbladen toevoegen *
prompt * aan Diva *
prompt * *
prompt * --------------------------------------------------------------------------- *
prompt * *
prompt * Wijzigingsgeschiedenis : *
prompt * *
prompt * auteur datum versie wijziging *
prompt * ----------------------- ---------- ------ --------------------------- *
prompt * Rob Kromwijk, DPG 28-11-2011 1.0.0 Initiele creatie *
prompt * Rob Kromwijk, DPG 01-12-2011 1.0.1 Controle ingebouwd *
prompt * *
prompt *******************************************************************************
prompt
prompt *******************************************************************************
prompt * srid toekennen aan geimporteerde kaartbladen in tijdelijke tabel *
prompt *******************************************************************************
update tmp_kaartblad t
set t.geom.sdo_srid = 90112;
commit;
prompt
prompt *******************************************************************************
prompt * overzicht aantal toe te voegen kaartbladen *
prompt *******************************************************************************
select t.naam
, count(*) as aantal_kaartbladen
from tmp_kaartblad t
group by t.naam;
accept l_continue char prompt 'Wilt u doorgaan met de verwerking? (J / N): '
prompt
prompt *******************************************************************************
prompt * bestaande bladindelingen bijwerken en nieuwe bladindelingen toevoegen *
prompt *******************************************************************************
declare
cursor c_bladindeling
is
select distinct p.id
, nvl(p.naam, upper(k.naam)) as naam
from pdt_bladindeling p
right
outer
join tmp_kaartblad k on upper(p.naam) = upper(k.naam)
order by p.id;
cursor c_kaartblad(p_naam in varchar2)
is
select t.kaartblad
, t.geom
from tmp_kaartblad t
where upper(t.naam) = p_naam;
l_big_id number;
l_aantal number;
l_controle1 number;
l_controle2 number;
l_succes varchar2(1) := 'N';
begin
-- diva verwerking starten, indien gebruikersantwoord 'J' is
if upper('&l_continue') = 'J' then
for r1 in c_bladindeling loop
-------------------------------------------------------------------------------
-- bestaande bladindeling
if r1.id is not null then
-- aanwezige kaartbladen verwijderen (niet bladindeling!)
delete from pdt_kaartblad
where big_id = r1.id;
for r2 in c_kaartblad(r1.naam) loop
-- nieuwe kaartbladen toevoegen
insert into pdt_kaartblad ( id
, naam
, big_id
, geometrie)
values ( pdt_kbd_seq.nextval
, r2.kaartblad
, r1.id
, r2.geom);
end loop;
-------------------------------------------------------------------------------
-- nieuwe bladindeling
elsif r1.id is null then
select pdt_big_seq.nextval into l_big_id
from dual;
-- nieuwe bladindeling toevoegen
insert into pdt_bladindeling ( id
, naam)
values ( l_big_id
, r1.naam);
dbms_output.put_line('toevoegen nieuwe bladindeling ' || r1.naam);
for r2 in c_kaartblad(r1.naam) loop
-- nieuwe kaartbladen toevoegen
insert into pdt_kaartblad ( id
, naam
, big_id
, geometrie)
values ( pdt_kbd_seq.nextval
, r2.kaartblad
, l_big_id
, r2.geom);
end loop;
end if;
-------------------------------------------------------------------------------
-- controle verwerking
select count(*) into l_controle1
from (select t.naam
, t.kaartblad
from tmp_kaartblad t
where upper(t.naam) = r1.naam
--
minus
--
select b.naam
, k.naam
from pdt_bladindeling b
join pdt_kaartblad k on b.id = k.big_id
where upper(b.naam) = r1.naam);
select count(*) into l_controle2
from (select b.naam
, k.naam
from pdt_bladindeling b
join pdt_kaartblad k on b.id = k.big_id
where upper(b.naam) = r1.naam
--
minus
--
select t.naam
, t.kaartblad
from tmp_kaartblad t
where upper(t.naam) = r1.naam);
if (l_controle1 = 0) and (l_controle2 = 0) then
-- telling aantal succesvolle toegevoegde rijen
select count(*) into l_aantal
from pdt_bladindeling b
join pdt_kaartblad k on b.id = k.big_id
where upper(b.naam) = r1.naam;
dbms_output.put_line('aantal toegevoegde ' || r1.naam || ' kaartbladen: ' || l_aantal);
else
dbms_output.put_line('FOUT OPGETREDEN: verschil in aantallen (verwerking wordt teruggedraaid)');
rollback;
exit;
end if;
end loop;
-------------------------------------------------------------------------------
else
dbms_output.put_line('Diva verwerking door gebruiker geannuleerd. Geen kaartbladen toegevoegd');
end if;
exception
when others then
rollback;
end;
/
commit;
prompt
prompt *******************************************************************************
prompt * tijdelijke tabel verwijderen *
prompt *******************************************************************************
drop table tmp_kaartblad;
prompt
prompt *******************************************************************************
prompt * einde script *
prompt *******************************************************************************
spool off;
exit;
|
|
|
|
| Re: Continue sql script: yes or no [message #534006 is a reply to message #534004] |
Fri, 02 December 2011 02:09 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Yes but you achieve a different thing that you asked for even if it does what you want it does, it is not what you asked: "But wether or not to continue to execute the script".
Here whatever is the answer you continue the script.
This is why it is better to ask what you want to achieve (the final goal) and not how to achieve the way you think to achieve your goal.
Regards
Michel
[Updated on: Fri, 02 December 2011 02:11] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
