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

oracle exception error-handling plsql

13,204

Solution 1

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.

Solution 2

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

13,204

Author by

Vishwas Mehra

Updated on June 23, 2022

Comments

Vishwas Mehra almost 2 years

(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.

Vishwas Mehra almost 15 years

Thanks but that just gives me a description of the error such as "invalid month" for date operations or "value too large" if the value exceeds the column size. I need to know which value caused that error so that I can make appropriate corrections to the data.
Rob van Wijk almost 15 years

-1 for suggesting to move from a single update statement to row-by-row (also known as slow-by-slow) processing.
Christian almost 15 years

OK Rob! You must have in mind that "you can't eat your cake and have it too". My solution works. What about yours?
Vishwas Mehra almost 15 years

Yep I had thought of iterating through them individually and getting it that way... it is a HUGE dataset though and was hoping to get the answer directly without having to iterate (and learn something new at the same time!). It is a good suggestion though, thanks!
Rob van Wijk almost 15 years

@Christian: as you can see on this page, my solution works as well :-)
Vishwas Mehra almost 15 years

I read through this, as well as your blog link and unless I am misunderstanding this still only gives me the error messages (ie. SQLCODE and SQLERRM) and not the data value triggering the error. That unfortunately isn't helpful. BTW I'm stuck with using 9g.
Vishwas Mehra almost 15 years

Note for anyone else trying to use this, you'll need to add 'FOR UPDATE' to the select on the cursor. This isn't the solution I was hoping for, but it seems to be the only means of actually getting the value based on the answers given here.
Rob van Wijk almost 15 years

If you look closely at the contents of err$_my_table, you'll notice my_column saying 12 and 14, which are the new values that failed to update. I will post another answer, specifically for your 9i version. Also because it hurts my eyes to see you chose a row-by-row approach :-)