RE: PL/SQL beginner

From: Robertson Lee - lerobe <Lee.Robertson_at_acxiom.com>
Date: Thu, 5 Feb 2009 21:12:21 -0000
Message-ID: <F1A191B056E5E04EA2134D842F9396F815216E78_at_sunmsx01.Corp.Acxiom.net>

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  

e.g  

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 ........  

Cheers
Lee (ps. Catherine says Hi)

---

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

Lee,

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.

Cheers,
Daniel Fink

-- 
Daniel Fink

OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and
Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/


test_whenever.sql
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 10.2.0.1.0 - Production on Thu Feb 5 13:20:33 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
_at_> _at_test_whenever
Connected.
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
10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Home\scripts\SQL>



Robertson Lee - lerobe wrote: 

	Hi,
	 
	Oracle 10gR2
	AIX 6
	 
	Within a SQL*Plus script I have a number of calls to PL/SQL
procs.
	 
	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
	 
	Lee
	 
	


************************************************************************

***
	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.
	


************************************************************************

****
	  




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 05 2009 - 15:12:21 CST