Home » SQL & PL/SQL » SQL & PL/SQL » Help!!!!!!!! to track exception in one single block for three different select statement......
Help!!!!!!!! to track exception in one single block for three different select statement...... [message #446457] Mon, 08 March 2010 09:55 Go to next message
amit.sonar
Messages: 93
Registered: December 2009
Location: Mumbai
Member
Hi all,

Can anybody tell me how can i track the exception for three select statement in one pl-sql block. here is synario.......

begin

select * from emp where empno=1234; --statement 1

select * from cusotmers where cust_id=125; --statement 2

select * from products where product_id='a-3'; --statement 3

end;

i want to track exception any one for ex no_data_found for all these three different statement.

I know if i put this three statement in three different pl-sql sublock then i can trap it....

how can i trap it in one single block??? Kindly help me on this.


Regards,
Amit Sonar.

Re: Help!!!!!!!! to track exception in one single block for three different select statement...... [message #446458 is a reply to message #446457] Mon, 08 March 2010 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

create three different EXCEPTION blocks; 1 for each SELECT
Re: Help!!!!!!!! to track exception in one single block for three different select statement...... [message #446463 is a reply to message #446457] Mon, 08 March 2010 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 10 Handling PL/SQL Errors,
especially paragraph "Scope Rules for PL/SQL Exceptions" and "How PL/SQL Exceptions Propagate".

Regards
Michel
Re: Help!!!!!!!! to track exception in one single block for three different select statement...... [message #446525 is a reply to message #446463] Tue, 09 March 2010 02:03 Go to previous messageGo to next message
athar.fitfd@hotmail.com
Messages: 193
Registered: October 2007
Location: pakistan
Senior Member
use individual sub blocks for each select.
Re: Help!!!!!!!! to track exception in one single block for three different select statement...... [message #446596 is a reply to message #446457] Tue, 09 March 2010 08:41 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that what you want (your question is a little on the incoherent side) is to identify which of your selects raises the exception then you could do this:
declare
  v_stmnt     pls_integer := 0; 
begin

v_stmnt :=1;
select * into <variable> from emp where empno=1234; --statement 1

v_stmnt :=2;
select * into <variable> from cusotmers where cust_id=125; --statement 2

v_stmnt :=3;
select * into <variable> from products where product_id='a-3'; --statement 3

exception
when no_Data_found then
 <code to handle exceptions. This code can use v_stmnt to
  identify which statement caused the error>
end;
Previous Topic: DBMS_JOB
Next Topic: Problem in creating body of package! (merged by CM)
Goto Forum:
  


Current Time: Tue Sep 27 21:11:41 CDT 2016

Total time taken to generate the page: 0.09049 seconds