Home » SQL & PL/SQL » Client Tools » Continue sql script: yes or no
Continue sql script: yes or no [message #533849] Thu, 01 December 2011 08:52 Go to next message
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 #533856 is a reply to message #533849] Thu, 01 December 2011 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 58898
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
The only thing you can do is Ctl-C to stop the script (and SQL*Plus).

Regards
Michel
Re: Continue sql script: yes or no [message #533870 is a reply to message #533849] Thu, 01 December 2011 09:14 Go to previous messageGo to next message
flyboy
Messages: 1770
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 #533894 is a reply to message #533870] Thu, 01 December 2011 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58898
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not aware about any way for conditional code execution/termination in sqlplus

There is none, the only way to exit a script is to exit SQL*Plus.

Regards
Michel
Re: Continue sql script: yes or no [message #533905 is a reply to message #533894] Thu, 01 December 2011 12:20 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Thanks for the reply. I think using ACCEPT can solve my problem.

[Updated on: Thu, 01 December 2011 12:21]

Report message to a moderator

Re: Continue sql script: yes or no [message #533913 is a reply to message #533905] Thu, 01 December 2011 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58898
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think not.

Regards
Michel
Re: Continue sql script: yes or no [message #533915 is a reply to message #533913] Thu, 01 December 2011 12:51 Go to previous messageGo to next message
bibber
Messages: 38
Registered: August 2006
Member
Based on the accept value, I can make an IF..THEN..ELSE statement, can't I?
Re: Continue sql script: yes or no [message #533917 is a reply to message #533915] Thu, 01 December 2011 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 22703
Registered: January 2009
Senior Member
>Based on the accept value, I can make an IF..THEN..ELSE statement, can't I?
ACCEPT is SQLPLUS command & SQLPLUS does not have/support IF THEN ELSE construct.
PL/SQL is different from SQL which is different from SQLPLUS
Re: Continue sql script: yes or no [message #533925 is a reply to message #533915] Thu, 01 December 2011 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58898
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bibber wrote on Thu, 01 December 2011 19:51
Based on the accept value, I can make an IF..THEN..ELSE statement, can't I?


There is no IF THEN ELSE in SQL*Plus.

Regards
Michel
Re: Continue sql script: yes or no [message #533938 is a reply to message #533925] Thu, 01 December 2011 15:06 Go to previous messageGo to next message
joy_division
Messages: 4504
Registered: February 2005
Location: East Coast USA
Senior Member
What about appending a condition
where 1=1
when user says YES
or
where 1=2
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 Go to previous messageGo to next message
Littlefoot
Messages: 19504
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58898
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Continue sql script: yes or no [message #534008 is a reply to message #534006] Fri, 02 December 2011 02:11 Go to previous message
Littlefoot
Messages: 19504
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't.
Previous Topic: Oracle SQL Developer Error message
Next Topic: HOW TO EXIT OUT OF SQLPLUS
Goto Forum:
  


Current Time: Wed Aug 27 03:59:56 CDT 2014

Total time taken to generate the page: 0.10541 seconds