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

Answer 2

A solution using the SAVE EXCEPTIONS clause:

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

Table created.

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  /

Function created.

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

Table altered.

SQL> declare
  2    e_forall_error exception;
  3    pragma exception_init(e_forall_error,-24381)
  4    ;
  5    type t_my_columns is table of my_table.my_column%type;
  6    a_my_columns t_my_columns := t_my_columns()
  7    ;
  8  begin
  9    select my_column
 10           bulk collect into a_my_columns
 11      from my_table
 12    ;
 13    forall i in 1..a_my_columns.count save exceptions
 14      update my_table
 15         set my_column = do_something(a_my_columns(i))
 16       where my_column = a_my_columns(i)
 17    ;
 18  exception
 19  when e_forall_error then
 20    for i in 1..sql%bulk_exceptions.count
 21    loop
 22      dbms_output.put_line(a_my_columns(sql%bulk_exceptions(i).error_index));
 23    end loop;
 24  end;
 25  /
2
4

PL/SQL procedure successfully completed.

For very large data sets, you probably don't want to blow up your PGA memory, so be sure to use the LIMIT clause in that case.

Answer 3

PL/SQL defines 2 global variables to refer to errors:

SQLERRM : SQL error Message

SQLERRNO: SQL error Number

This is readable in the EXCEPTION block in your PL/SQL.

DECLARE 
    x number;
BEGIN
    SELECT 5/0 INTO x FROM DUAL;
EXCEPTION
    WHEN OTHERS THEN:
        dbms_output.put_line('Error Message: '||SQLERRM);
        dbms_output.put_line('Error Number: '||SQLERRNO);
END;

Answer 4

Try this (not tested):

    DECLARE
      cursor c1 is
        select key_column, my_column
        from my_table
        WHERE my_column IS NOT NULL
        ORDER BY key_column;

      my_table_rec my_table%ROWTYPE;

    BEGIN

      FOR my_table_rec in c1
      LOOP
        UPDATE my_table SET my_column = do_something(my_column)
            WHERE key_column = my_table_rec.key_column;
      END LOOP;

    EXCEPTION
        when others then
        insert into log_error (msg) values ('Error updating key_column: ' || my_table_rec.key_column || ', my_column: ' || my_table_rec.my_column);
    END;

Answer 5

For more detailing information regarding how execution arrived at the line in question, you could try displaying the output returned by these functions:

• DBMS_UTILITY.format_error_stack:

  Format the current error stack. This can be used in exception handlers to look at the full error stack.

• DBMS_UTILITY.format_error_backtrace:

  Format the backtrace from the point of the current error to the exception handler where the error has been caught. NULL string is returned if no error is currently being handled.

Answer 6

try outputting your error and see if it gives you the information you are looking for. For example:

EXCEPTION
    WHEN OTHERS
    THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;