RE: PL/SQL beginner

From: Robertson Lee - lerobe <>
Date: Thu, 5 Feb 2009 21:12:21 -0000
Message-ID: <>

Cheers Dan, I won;t be jumping off any mountains, trust me :-)  

I don't want to exit from from sqlplus. what I want to do and what doesn't appear to work is  


sql script

whenever sqlerror exit
call proc1
call proc2
call proc3    

If something goes wrong in proc1 I don't want it to call anymore procs and pass back the error to the original calling sql script. ?  

similarly, if all is ok woth proc1, then call proc2, if something goes wrong with proc2 don't call proc3 and pass back error to the calling sql script.  

Hope things are good at your end matey ........  

Lee (ps. Catherine says Hi)

From: Daniel Fink [] Sent: 05 February 2009 19:28
To: Robertson Lee - lerobe
Cc: oracle-l
Subject: Re: PL/SQL beginner


Before you go jumping off a mountain in frustration and humiliation....

Unfortunately, that functionality does not exist in SQL*Plus. The only options we have are EXIT or CONTINUE example below). It would really be nice if there was an option 'HALT', 'SUSPEND', 'STOP' that would stop executing the script, but not take you all the way out of sql*plus. Considering the emphasis on SQL*Developer and the 11gr1 removal of sqlplus for windows, I highly doubt Oracle will be doing any enhancements.

Daniel Fink

Daniel Fink - Oracle Performance, Diagnosis, Data Recovery and

Oracle Blog

Lost Data?

connect demo/demo
whenever sqlerror continue
select 'CONTINUE' from daul;
whenever sqlerror continue none
select 'CONTINUE NONE' from daul;
whenever sqlerror exit
select 'EXIT' from daul;

C:\Home\scripts\SQL>sqlplus /nolog
SQL*Plus: Release - Production on Thu Feb 5 13:20:33 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
_at_> _at_test_whenever
select 'CONTINUE' from daul
ERROR at line 1:
ORA-00942: table or view does not exist

select 'CONTINUE NONE' from daul
ERROR at line 1:
ORA-00942: table or view does not exist

select 'EXIT' from daul
ERROR at line 1:
ORA-00942: table or view does not exist

Disconnected from Oracle Database 10g Enterprise Edition Release - Pr
With the Partitioning, OLAP and Data Mining options


Robertson Lee - lerobe wrote: 

	Oracle 10gR2
	AIX 6
	Within a SQL*Plus script I have a number of calls to PL/SQL
	I want to, on failure of any of the PL/SQL scripts, drop back to
the calling SQL*Plus script and on return not go any further...i.e do
not call any of the other Stored Procs in the SQL*PLus script.
	Ideally I would also like to report back to the calling SQL*Plus
script the errors from the failed PL/SQL.
	I cannot re-engineer, I just need to make what is already there
work if it is at all possible.
	Any suggestions after you have all stopped laughing ??   !!!
	Regards and TIA

*** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You.
**** --
Received on Thu Feb 05 2009 - 15:12:21 CST

Original text of this message