How to determine row/value throwing error in PL/SQL statement?

Question

(Oracle PL/SQL)

If I have a simple SQL statement that is throwing an error, ie:

DECLARE
    v_sql_errm varchar2(2048);
BEGIN
    UPDATE my_table SET my_column = do_something(my_column)
        WHERE my_column IS NOT NULL;
EXCEPTION
    when others then
        -- How can I obtain the row/value causing the error (unknown)?
        v_sql_errm := SQLERRM;
        insert into log_error (msg) values ('Error updating value (unknown): '||
             v_sql_errm);
END;

Is there any way within the exception block to determine the row/value on which the query is encountering an error? I would like to be able to log it so that I can then go in and modify/correct the specific data value causing the error.

Answer 1

This can be done using DML error logging, if you are on 10gR2 or later.

An example:

SQL> create table my_table (my_column)
  2  as
  3  select level from dual connect by level <= 9
  4  /

Tabel is aangemaakt.

SQL> create function do_something
  2  ( p_my_column in my_table.my_column%type
  3  ) return my_table.my_column%type
  4  is
  5  begin
  6    return 10 + p_my_column;
  7  end;
  8  /

Functie is aangemaakt.

SQL> alter table my_table add check (my_column not in (12,14))
  2  /

Tabel is gewijzigd.

SQL> exec dbms_errlog.create_error_log('my_table')

PL/SQL-procedure is geslaagd.

This creates an error logging table called err$_my_table. This table is filled by adding a log errors clause to your update statement:

SQL> begin
  2    update my_table
  3       set my_column = do_something(my_column)
  4     where my_column is not null
  5           log errors reject limit unlimited
  6    ;
  7  end;
  8  /

PL/SQL-procedure is geslaagd.

SQL> select * from err$_my_table
  2  /

                       ORA_ERR_NUMBER$
--------------------------------------
ORA_ERR_MESG$
--------------------------------------------------------------------
ORA_ERR_ROWID$
--------------------------------------------------------------------
OR
--
ORA_ERR_TAG$
--------------------------------------------------------------------
MY_COLUMN
--------------------------------------------------------------------
                                  2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAB
U

12

                                  2290
ORA-02290: check constraint (RWK.SYS_C00110133) violated
AAGY/aAAQAABevcAAD
U

14


2 rijen zijn geselecteerd.

Prior to 10gR2, you can use the SAVE EXCEPTIONS clause: http://rwijk.blogspot.com/2007/11/save-exceptions.html