RE: PL/SQL beginner

From: Robertson Lee - lerobe <Lee.Robertson_at_acxiom.com>
Date: Fri, 6 Feb 2009 14:54:47 -0000
Message-ID: <F1A191B056E5E04EA2134D842F9396F815216E85_at_sunmsx01.Corp.Acxiom.net>

Nope, checked through all of that and it looks good to go :-)  

Thanks everyone for your comments and sage advice.

---

From: Goulet, Richard [mailto:Richard.Goulet_at_parexel.com] Sent: 06 February 2009 14:40
To: Niall Litchfield; Robertson Lee - lerobe Cc: oracle-l
Subject: RE: PL/SQL beginner

Have to agree with Niall on that. If the procedure catches the error internally and forgets it then your screwed no matter what you try to do. Course you did say you could not re-invent the wheel here, just adapt. So your at the mercy of the duhveloper who may or may not have simply left error handling to someone else too. Think of the number of PL/SQL packages that don't include the exception handler at all.  

Dick Goulet

"Often it's not the Boat that needs overhauling, but the Skipper" Unknown

---

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Friday, February 06, 2009 1:22 AM
To: Lee.Robertson_at_acxiom.com
Cc: Goulet, Richard; oracle-l
Subject: Re: PL/SQL beginner

watchout for procedures that already 'handle' errors, esepcially those that handle them with "when others then null" a.k.a "forget anything happened, move along, these are not your errors" . You wouldn't think pl/sql developers used vb practices, but they do.  

Niall

On Thu, Feb 5, 2009 at 9:41 PM, Robertson Lee - lerobe <Lee.Robertson_at_acxiom.com> wrote:

        Woo Hoo !!!!!!          

        Thanks a bunch, that does exactly what I am after          

        Virtual beer on its way mate !!                    

---

	From: Goulet, Richard [mailto:Richard.Goulet_at_parexel.com] 
	Sent: 05 February 2009 20:53
	To: Robertson Lee - lerobe; oracle-l
	Subject: RE: PL/SQL beginner
	
	
	Lee,
	 
	    It may be possible, but then it could also backfire so trail

is necessary, but you could try:          

	set serverout on size unlimited
	begin
	   <procedure1>;
	    <procedure2>;
	exception
	   when others then dbms_output.put_line(sqlerrm);
	end;
	/
	 
	This should allow the procedures to run one at a time in serial,

but if an error occurs the pl/sql block will catch it, report the error, and then exit back to sqlplus gracefully.          

        Dick Goulet          

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Robertson Lee - lerobe

	Sent: Thursday, February 05, 2009 12:08 PM
	To: oracle-l
	Subject: PL/SQL beginner
	
	
	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.         

---

---

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


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

Received on Fri Feb 06 2009 - 08:54:47 CST