BEGIN -- of block for first Select<= /DIV>

SELECT ...

EXCEPTION -- Exception handler for first= S elect

      WHEN NO_DATA_FOUND THEN=20
         INSE= RT INTO=20 errors VALUES ('Error in First Select statement ' || Error Code & Numbe= r=20 );
  = E ND;   -- of block for first Select

-- other code

BEGIN -- of block for second Select=

SELECT ...

EXCEPTION -- Exception handler for se= cond=20 Select

      WHEN NO_DATA_FOUND THEN=20
         INSE= RT INTO=20 errors VALUES ('Error in Second Select statement ' || Error Code & Numb= er=20 );
  = E ND;   -- of block for second Select

-- other code

BEGIN -- of block for Third Select<= /DIV>

SELECT ...

EXCEPTION -- Exception handler for Third= S elect

      WHEN NO_DATA_FOUND THEN=20
         INSE= RT INTO=20 errors VALUES ('Error in Third Select statement ' || Error Code & Numbe= r=20 );
  = E ND;   -- of block for Third Select

-- other code

END; -- of main=20 block

Hope this helps ..

Regards
Oweson=20 Flynn
------------------------------------------------------------------=
Certified=20 Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011)=20 782-9313
EMail: oef@icon.co.za

----- Original Message -----
Fro= m:=20 Sipka P=E9te= r=20

To: Multiple recipients of list ORACLE-L= =20

Sent: Thursday, January 04, 2001 1= 1:55=20 AM

Subject: Detecting the line of the= Error=20 in a PL/SQL Code

Hi=20 Gurus!=20

I've a very interesting question about the Error Handling in the ORACL= E.=20

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

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

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

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

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:=20

DECLARE
   stmt I= NTEGER :=3D=20 1; -- designates 1st SELECT statement
BEGIN
   SELECT ...=
   stmt :=3D 2; -- designates 2nd SEL= ECT=20 statement
   SELECT ...
<= FONT=20 size=3D-1>   stmt :=3D 3; -- designates 3rd SELECT=20 statement
   SELECT ...
<= FONT=20 size=3D-1>   ...
EXCEPTION    WHEN NO_DATA_FOUND THEN
      INSERT INTO errors VALUES ('Erro= r in=20 statement ' || stmt);
    = =20 ...
END;
=20

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

Peter Sipka

<=
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=