Re: Detecting the line of the Error in a PL/SQL Code

From: Oweson Flynn <Oweson.Flynn_at_liberty.co.za>
Date: Thu, 4 Jan 2001 12:24:27 +0200
Message-Id: <10731.125685@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_003F_01C07649.4797E140 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi Peter

The 'Eleant' (or certainly correct way) to write the code is in anonymous P= L/SQL blocks. So the correct stucture of the code segment you gave is (in = order to hadle the errors elagantly) is

BEGIN -- of main block

BEGIN -- of block for first Select

SELECT ...
EXCEPTION -- Exception handler for first Select

      WHEN NO_DATA_FOUND THEN=20
         INSERT INTO errors VALUES ('Error in First Select statement ' || E=

rror Code & Number );=20

END; -- of block for first Select

other code

BEGIN -- of block for second Select

SELECT ...
EXCEPTION -- Exception handler for second Select

      WHEN NO_DATA_FOUND THEN=20
         INSERT INTO errors VALUES ('Error in Second Select statement ' || =

Error Code & Number );=20

END; -- of block for second Select

other code

BEGIN -- of block for Third Select

SELECT ...
EXCEPTION -- Exception handler for Third Select

      WHEN NO_DATA_FOUND THEN=20
         INSERT INTO errors VALUES ('Error in Third Select statement ' || E=

rror Code & Number );=20

END; -- of block for Third Select

other code

END; -- of main block

Hope this helps ..

Regards
Oweson Flynn

Certified Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: oef_at_icon.co.za

Original Message -----=20 From: Sipka P=E9ter=20 To: Multiple recipients of list ORACLE-L=20 Sent: Thursday, January 04, 2001 11:55 AM Subject: Detecting the line of the Error in a PL/SQL Code

Hi Gurus!=20
I've a very interesting question about the Error Handling in the ORACLE.=

Is there a better way to detect the line of the statement which cause the= Error than insert a lot of 'variable :=3D something' like statement in th= e code....=20

I've found the following guidelies in the oracle documentation, which sug= gest that solution:=20

Exceptions can mask the statement that caused an error, as the following = example shows:=20

BEGIN=20

     SELECT ...=20
     SELECT ...=20
     SELECT ...=20
     ...=20
  EXCEPTION=20
     WHEN NO_DATA_FOUND THEN ...=20
        -- Which SELECT statement caused the error?=20

END;=20 Normally, this is not a problem. But, if the need arises, you can use a l= ocator variable to track statement execution, as follows:=20

DECLARE=20
stmt INTEGER :=3D 1; -- designates 1st SELECT statement=20 BEGIN=20

     SELECT ...=20
     stmt :=3D 2;  -- designates 2nd SELECT statement=20
     SELECT ...=20
     stmt :=3D 3;  -- designates 3rd SELECT statement=20
     SELECT ...=20
     ...=20
  EXCEPTION=20
     WHEN NO_DATA_FOUND THEN=20
        INSERT INTO errors VALUES ('Error in statement ' || stmt);=20
        ...=20

END;=20
=20

Is there any other built in function which can solve this problem, becaus= e this is not a nice solution?=20
Thank you for your helps in advance....=20

Peter Sipka=20

This message may contain information which is confidential and subject to l=
egal privilege. If you are not the intended recipient, you may not peruse, =
use, disseminate, distribute or copy this message. If you have received thi=
s message in error, please notify the sender immediately by email, facsimil=

e or telephone and return and/or destroy the original message.

------=_NextPart_000_003F_01C07649.4797E140 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi Peter</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>The 'Eleant' (or certainly correct way) to write the co=

de is=20
in anonymous PL/SQL blocks.  So the correct stucture of the code segme= nt=20
you gave is (in order to hadle the errors elagantly) is</FONT></DIV>

<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>BEGIN -- of main block</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; BEGIN -- of block for first Select</FONT><=
/DIV>

<DIV><FONT size=3D2>      SELECT ...</FONT></DIV> <DIV><FONT size=3D2>   EXCEPTION -- Exception handler for first= S
elect</FONT></DIV>
<DIV><FONT size=3D2>      WHEN NO_DATA_FOUND THEN<FONT = size=3D3>=20
<BR></FONT><FONT size=3D-1>         INSE= RT INTO=20
errors VALUES ('Error in First Select statement ' || Error Code & Numbe= r=20
);</FONT><FONT size=3D3> </FONT><BR></FONT><FONT size=3D2>  = E
ND;   -- of block for first Select</FONT></DIV>

<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>
<DIV><FONT size=3D2>&nbsp; -- other code</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; BEGIN -- of block for second Select</FONT>=
</DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT ...</FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; EXCEPTION -- Exception handler for&nbsp;se=

cond=20
Select</FONT></DIV>
<DIV><FONT size=3D2>      WHEN NO_DATA_FOUND THEN<FONT = size=3D3>=20
<BR></FONT><FONT size=3D-1>         INSE= RT INTO=20
errors VALUES ('Error in Second Select statement ' || Error Code & Numb= er=20
);</FONT><FONT size=3D3> </FONT><BR></FONT><FONT size=3D2>  = E
ND;   -- of block for second Select</FONT></DIV>

<DIV><FONT size=3D2>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>
<DIV><FONT size=3D2>&nbsp; -- other code</FONT></DIV>
<DIV>&nbsp;</DIV></FONT></DIV></FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; BEGIN -- of block for Third Select</FONT><=
/DIV>

<DIV><FONT size=3D2>      SELECT ...</FONT></DIV> <DIV><FONT size=3D2>   EXCEPTION -- Exception handler for Third= S
elect</FONT></DIV>
<DIV><FONT size=3D2>      WHEN NO_DATA_FOUND THEN<FONT = size=3D3>=20
<BR></FONT><FONT size=3D-1>         INSE= RT INTO=20
errors VALUES ('Error in Third Select statement ' || Error Code & Numbe= r=20
);</FONT><FONT size=3D3> </FONT><BR></FONT><FONT size=3D2>  = E
ND;   -- of block for Third Select</FONT></DIV>

<DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>
<DIV><FONT size=3D2>&nbsp; -- other code</FONT></DIV>
<DIV>&nbsp;</DIV></FONT></DIV></DIV>END;&nbsp;&nbsp; -- of main=20
block</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>Hope this helps ..</FONT></DIV>

<DIV><FONT size=3D2></FONT> </DIV>
<DIV>Regards<BR>Oweson=20

Flynn<BR>------------------------------------------------------------------=

<BR>Certified=20
Oracle DBA<BR>The Flynn Consultancy<BR>Tel: 082-600-7-006<BR>Fax: (011)=20 782-9313<BR>EMail: <A href=3D"mailto:oef_at_icon.co.za">oef_at_icon.co.za</A></DI= V>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE= FT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>Fro= m:</B>=20
  <A title=3Dpsipka_at_pantel.hu href=3D"mailto:psipka_at_pantel.hu">Sipka P=E9te= r</A>=20
</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3DORACLE-L_at_fatcity.co= m=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L= </A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, January 04, 2001 1= 1:55=20
  AM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Detecting the line of the= Error=20
  in a PL/SQL Code</DIV>
  <DIV><FONT size=3D2></FONT><FONT size=3D2></FONT><FONT size=3D2></FONT><B= R></DIV>Hi=20
  Gurus!=20
  <P>I've a very interesting question about the Error Handling in the ORACL= E.=20
  <P>Is there a better way to detect the line of the statement which cause = the=20
  Error than insert a lot of  'variable :=3D something' like statement= in the=20
  code....=20
  <P>I've found the following guidelies in the oracle documentation, which=

  suggest that solution:=20
  <P><FONT size=3D-1>Exceptions can mask the statement that caused an error= , as=20
  the following example shows:</FONT><FONT size=3D-1></FONT>=20

  <P><FONT size=3D-1>BEGIN</FONT> <BR><FONT size=3D-1>&nbsp;&nbsp; SELECT .=
..</FONT>=20
  <BR><FONT size=3D-1>&nbsp;&nbsp; SELECT ...</FONT> <BR><FONT=20

size=3D-1>   SELECT ...</FONT> <BR><FONT size=3D-1>  =

  ...</FONT> <BR><FONT size=3D-1>EXCEPTION</FONT> <BR><FONT size=3D-1>&nbsp= ; =20
  WHEN NO_DATA_FOUND THEN ...</FONT> <BR><FONT=20   size=3D-1>      -- Which SELECT statement caused= the=20
  error?</FONT> <BR><FONT size=3D-1>END;</FONT><FONT size=3D-1></FONT>=20   <P><FONT size=3D-1>Normally, this is not a problem. But, if the need aris= es, you=20
  can use a locator variable to track statement execution, as=20   follows:</FONT><FONT size=3D-1></FONT>=20   <P><FONT size=3D-1>DECLARE</FONT> <BR><FONT size=3D-1>   stmt I= NTEGER :=3D=20
  1;  -- designates 1st SELECT statement</FONT> <BR><FONT=20   size=3D-1>BEGIN</FONT> <BR><FONT size=3D-1>   SELECT ...</FONT>=

  <BR><FONT size=3D-1>   stmt :=3D 2;  -- designates 2nd SEL= ECT=20
  statement</FONT> <BR><FONT size=3D-1>   SELECT ...</FONT> <BR><= FONT=20
  size=3D-1>   stmt :=3D 3;  -- designates 3rd SELECT=20   statement</FONT> <BR><FONT size=3D-1>   SELECT ...</FONT> <BR><= FONT=20
  size=3D-1>   ...</FONT> <BR><FONT size=3D-1>EXCEPTION</FONT> <B= R><FONT=20
  size=3D-1>   WHEN NO_DATA_FOUND THEN</FONT> <BR><FONT=20   size=3D-1>      INSERT INTO errors VALUES ('Erro= r in=20
  statement ' || stmt);</FONT> <BR><FONT size=3D-1>    =  =20
  ...</FONT> <BR><FONT size=3D-1>END;</FONT> <BR> =20   <P>Is there any other built in function which can solve this problem, bec= ause=20
  this is not a nice solution? <BR>Thank you for your helps in advance....=

<P>    Peter Sipka </P></BLOCKQUOTE><CODE><FONT SIZE=3D3><= BR>
<BR>

********************************************************************<BR>

<BR>

This message may contain information which is confidential and subject to l=
egal privilege. If you are not the intended recipient, you may not peruse, =
use, disseminate, distribute or copy this message. If you have received thi=
s message in error, please notify the sender immediately by email, facsimil=

Received on Thu Jan 04 2001 - 04:24:27 CST