OraFAQ Forum: SQL & PL/SQL » re-raising the exception.

Home » SQL & PL/SQL » SQL & PL/SQL » re-raising the exception. (oracle 11g , Hp UNIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

re-raising the exception. [message #614792]

Tue, 27 May 2014 07:37

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Hi All.. I am new to plsql programming .

Want to understand in what condition we need to go for re-raise'ing the exception .
In many of the procedures I have seen exception block like this -

Exception
WHEN others THEN
ln_errnum := SQLCODE;
ls_errmsg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('error num is '||ln_errnum ||'error msg'||ls_errmsg ) ;
RAISE;

I am not getting what is the need of RAISE the same error again . What if we not use RAISE here ? When we should go for RAISE ?

I tried searching on google but didn't get satisfactory answer .

Please share your experience . Thanks a lot.

Report message to a moderator

Re: re-raising the exception. [message #614795 is a reply to message #614792]

Tue, 27 May 2014 07:41

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You need to use RAISE when you have to handle an exception (for instance to log it or release some resources) and then to re-raise it to the caller or outer block.
If you don't use RAISE then the exception is gobbled by your exceptionhandler and the caller/outer block does not know it appears.
So the condition to use RAISE is to answer: "does the caller/outer block need to know there was this exception?"

Note that the posted code is bad; read WHEN OTHERS.

Report message to a moderator

Re: re-raising the exception. [message #614871 is a reply to message #614795]

Wed, 28 May 2014 01:16

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Thanks Michel .. Got it .. Thanks!

Report message to a moderator

Re: re-raising the exception. [message #614877 is a reply to message #614871]

Wed, 28 May 2014 02:35

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

If the table name is not correct(does not exist in database) or variable used in select into statement is not defined then the error is not coming to exception block . How we can catch those exception ?

EX:
03:15:52 SQL> begin
03:15:59 2 insert into select * from TESTART;
03:16:19 3 exception
03:16:25 4 when others then
03:16:34 5 dbms_output.put_line('error is :'||sqlcode||':'||sqlerrm);
03:17:16 6 end;
03:17:19 7 /
exception
*
ERROR at line 3:
ORA-06550: line 2, column 13:
PL/SQL: ORA-00903: invalid table name
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored

EX:
03:17:20 SQL> begin
03:31:22 2 select id into var from TESTART;
03:31:53 3 dbms_output.put_line('check point 1');
03:32:41 4 exception
03:32:48 5 when others then
03:32:55 6 dbms_output.put_line('error is :'|| sqlerrm);
03:33:15 7 end;
03:33:18 8 /
select id into var from TESTART;
*
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00201: identifier 'VAR' must be declared
ORA-06550: line 2, column 20:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored

Report message to a moderator

Re: re-raising the exception. [message #614879 is a reply to message #614877]

Wed, 28 May 2014 03:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is a compile time error not an execution one, you cannot trap it.
But, anyway, it does not matter, you have it ONLY when you write the code NOT when the client executes it.
In this case, just fix the code.

Report message to a moderator

Re: re-raising the exception. [message #614887 is a reply to message #614879]

Wed, 28 May 2014 04:41

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

ohh.. gotcha ..Thanks again michel..

Report message to a moderator

Re: re-raising the exception. [message #614904 is a reply to message #614887]

Wed, 28 May 2014 09:29

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Here you can read about handling plsql exception and re-raising them.

Report message to a moderator

Re: re-raising the exception. [message #614905 is a reply to message #614904]

Wed, 28 May 2014 09:35

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Want to understand in what condition we need to go for re-raise'ing the exception .
There is no need for any exception handler & therefore no need to re-raise anything.

Report message to a moderator

Re: re-raising the exception. [message #614907 is a reply to message #614905]

Wed, 28 May 2014 09:44

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not fully true, imo, see http://www.orafaq.com/forum/mv/msg/192362/612698/#msg_612698

Report message to a moderator

Re: re-raising the exception. [message #614908 is a reply to message #614907]

Wed, 28 May 2014 09:57

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Even documentation demonstrates such a scenario where we need to reraise an exception:

Quote:

Sometimes, you want to reraise an exception, that is, handle it locally, then pass it to an enclosing block. For example, you might want to roll back a transaction in the current block, then log the error in an enclosing block.

Above quote is from here.

Report message to a moderator

Previous Topic:	ORA-03113: end-of-file on communication channel
Next Topic:	multiple values in a single column should be populated as multiple rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:48:42 CDT 2024