Home » SQL & PL/SQL » SQL & PL/SQL » Execption handling
Execption handling [message #340625] Wed, 13 August 2008 08:16 Go to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Hi All,

How to Hanlde Exception that occur in Declaration part of a PL/SQL Block.

Follwoing is the code to Handle Exception raised in execution section.

SQL> DECLARE
2 value number := 10;
3 res number ;
4 BEGIN
5 value := value / 0;
6 EXCEPTION
7 WHEN
8 ZERO_DIVIDE then
9 dbms_output.put_line('ERROR in DIVISION');
10 END;
11
12 /
ERROR in DIVISION

PL/SQL procedure successfully completed.
Re: Execption handling [message #340626 is a reply to message #340625] Wed, 13 August 2008 08:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't catch an exception in the DECLARE section of a pl/sql block in that block's exception handler.
You can only catch it at the level that called the pl/sql block.
Re: Execption handling [message #340627 is a reply to message #340625] Wed, 13 August 2008 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enclose your block into an outer one:
SQL> declare
  2    v number := 1/0;
  3  begin
  4    null;
  5  exception when zero_divide then dbms_output.put_line('ERROR in DIVISION');
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 2

SQL> begin
  2    declare
  3      v number := 1/0;
  4    begin
  5      null;
  6    end;
  7  exception when zero_divide then dbms_output.put_line('ERROR in DIVISION');
  8  end;
  9  /
ERROR in DIVISION

PL/SQL procedure successfully completed.

Regards
Michel
Re: Execption handling [message #340634 is a reply to message #340627] Wed, 13 August 2008 09:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Even better: only do simple (constants) assignments in your declaration part.
I never understood the amount of logic some people try to cram into the declaration of a variable.
Re: Execption handling [message #340783 is a reply to message #340634] Thu, 14 August 2008 04:54 Go to previous message
panyam
Messages: 146
Registered: May 2008
Senior Member
Thanks Michel & Frank .
Previous Topic: Select The Max year and their Month
Next Topic: Procedure getting hanged
Goto Forum:
  


Current Time: Mon Dec 05 13:02:06 CST 2016

Total time taken to generate the page: 0.18570 seconds