How to detect throw / value throw error in PL / SQL expression?

Question

(Oracle PL / SQL)

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

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 in the exception block to determine the string / value that the request is encountering an error? I would like to be able to register it so that I can then log in and change / correct the specific data value causing the error.

Answer 1

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 datasets, you probably don't want to explode your PGA memory, so be sure to use the LIMIT clause in this case.

Answer 2

This can be done by logging DML errors if you are on 10gR2 or later.

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 log table named err $ _my_table. This table is populated by adding a suggestion about log errors to the 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. 

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

Answer 3

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

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

Answer 4

For more information on how this line is executed, you can 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 view the full stack of errors.

• DBMS_UTILITY.format_error_backtrace :

  Format the traceback from the point of the current error to the exception handler where the error was detected. A NULL string is returned if no error is currently being processed.

Answer 5

Try this (not verified):

  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 6

PL / SQL defines 2 global variables to indicate errors:

SQLERRM: SQL error message

SQLERRNO: SQL error number

This can be read in the EXCEPTION block in 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;