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

From: Sipka Péter <psipka_at_pantel.hu>
Date: Thu, 04 Jan 2001 10:49:33 +0100
Message-Id: <10731.125684@fatcity.com>

--------------C7E6F69AD8D276D5E04B9146
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hi Gurus!

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 := something' like statement in the code....

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

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

BEGIN

   SELECT ...
   SELECT ...
   SELECT ...

...
EXCEPTION
WHEN NO_DATA_FOUND THEN ...

Which SELECT statement caused the error? END;

Normally, this is not a problem. But, if the need arises, you can use a locator variable to track statement execution, as follows:

DECLARE
   stmt INTEGER := 1; -- designates 1st SELECT statement BEGIN
   SELECT ...
   stmt := 2; -- designates 2nd SELECT statement    SELECT ...
   stmt := 3; -- designates 3rd SELECT statement    SELECT ...
   ...
EXCEPTION
   WHEN NO_DATA_FOUND THEN

      INSERT INTO errors VALUES ('Error in statement ' || stmt);
      ...

END; Is there any other built in function which can solve this problem, because this is not a nice solution?
Thank you for your helps in advance....

Peter Sipka

--------------C7E6F69AD8D276D5E04B9146
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>

Hi Gurus!
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 := something' like statement in the code....
I've found the following guidelies in the oracle documentation, which
suggest that solution:
Exceptions can mask the statement that caused an error,
as the following example shows:
BEGIN
    SELECT ...
    SELECT ...
    SELECT ...
    ...
 EXCEPTION
    WHEN NO_DATA_FOUND THEN ...
       -- Which SELECT statement
caused the error?
 END;
Normally, this is not a problem. But, if the need arises,
you can use a locator variable to track statement execution, as follows:
DECLARE
    stmt INTEGER := 1;  -- designates 1st
SELECT statement
 BEGIN
    SELECT ...
    stmt := 2;  -- designates 2nd SELECT
statement
    SELECT ...
    stmt := 3;  -- designates 3rd SELECT
statement
    SELECT ...
    ...
 EXCEPTION
    WHEN NO_DATA_FOUND THEN
       INSERT INTO errors VALUES
('Error in statement ' || stmt);
       ...
 END;
  
Is there any other built in function which can solve this problem, because
this is not a nice solution?
 Thank you for your helps in advance....
Received on Thu Jan 04 2001 - 03:49:33 CST