Re: PL/SQL Exceptions value

From: Alan Mullett <alan_at_amm.powernet.co.uk>
Date: 1995/10/31
Message-ID: <476av2$qqo_at_power1.powernet.co.uk>#1/1


Yes, there are two system macros you can use:

  • SQLCODE - this is the current exception code only valid in the exception block
  • SQLERRM([SQLCODE]) - this will return a textual message related to the sqlcode. This can be used anywhere, and inside a exception block the sqlcode is optional.

so you could say

.....
exception

   when others then

      error_code := SQLCODE;
      error_text := SQLERRM;
      insert into error_log
      values(error_log_sequence.nextval, error_code, error_text)
end;
...

Note that these two macros cannot be used inside of the insert statement which is why the two variables are used. User defined exceptions are handled but you should use the exception_init pragma to be sure.

msahoo_at_indyvax.iupui.edu wrote:

>PL/SQL Question:
 

>Hello all,
 

>This may be too easy (sorry!).
 

>Is there a system variable in PL/SQL that holds the current exception
>value?
 

>Basically, I am processing some data and updating tables. If an error
>or exception occurs for which I do not have a trap, then with the
>aid of "WHEN OTHERS...", I want to store the error in an error log
>table.
 

>Can this be done in PL/SQL?
 

>thanks in advance,
>Manash

+-------------------------------+
|Alan M. Mullett                |

|Email : alan_at_amm.powernet.co.uk|
+-------------------------------+
Received on Tue Oct 31 1995 - 00:00:00 CET

Original text of this message