ORACLE capture exception object.

Question

Hi there any way to capture the objects (such as table and column) where an exception was thrown in ORACLE?

I need to determine the object name to customize the error message showing the user the name of the table and field where the exception occurred.

I know there is a variable SQLCODE and SQLERRM, but I wonder if there is any additional variable or a function that returns me the name of the error object.

i want something like this

exception
  when others then
    begin
     if SQLCODE = -20010
       then dbms_output.put_line('The Value Too Large in the field ' || GetObjectNameError);
     end if;
   end;

UPDATE

using the tony example

CREATE TABLE t (v varchar2(3));
COMMENT ON TABLE t IS 'my table description';
COMMENT ON COLUMN t.v IS 'my column description';
insert into t values ('xxxx');

Actually raise this error *

ERROR at line 1:
ORA-12899: value too large for column "MYSCHEMA"."T"."V" (actual: 4, maximum: 3)

I want to show something like this

ORA-12899: value too large for column "my column description" in table "my table description" (actual: 4, maximum: 3)

thanks in advance.

Answer 1

No, there isn't. But more recent versions of Oracle (10G at least) does it for you with its own exceptions:

SQL> create table t (v varchar2(3));

Table created.

SQL> insert into t values ('xxxx');
insert into t values ('xxxx')
                      *
ERROR at line 1:
ORA-12899: value too large for column "MYSCHEMA"."T"."V" (actual: 4, maximum: 3)

To get what you are looking for you could parse this error to get the table and column names (T and V in this example) and then look up the comments from USER_TAB_COMMENTS and USER_COL_COMMENTS and re-construct the message using those.

Answer 2

No.

Well, the raise_application_exception() allows you two items, the error code and a single text message. If you are throwing your own exceptions you could format the message to contain this information and parse it out.

The alternative is to put this information into a global "ERROR TABLE", then raise the exception.

To followup on comment below, oracle 10G includes:

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

This allows you to parse through the multiple raised errors. This blog post shows how to parse this information to extract more and better information from the call stack. But it still requires parsing the error messages rather than including your own.

Answer 3

Yes, you can do that, but only for source code locations. Check out Dbms_Utility.Format_Error_Stack() and Dbms_Utility.Format_Call_Stack().

You get a string containing the line number(s) and object name(s) of the piece(s) of PL/SQL where the error happened.