Wie kann man feststellen, ob eine Zeile/Wert einen Fehler in einer PL/SQL-Anweisung verursacht?

Question

(Oracle PL/SQL)

Wenn ich eine einfache SQL-Anweisung habe, die einen Fehler auslöst, d.h.:

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;

Gibt es eine Möglichkeit, innerhalb des Ausnahmeblocks die Zeile/den Wert zu bestimmen, bei der/dem die Abfrage auf einen Fehler stößt? Ich möchte den Fehler protokollieren können, damit ich dann den spezifischen Datenwert, der den Fehler verursacht, ändern/korrigieren kann.

Answer 1

Dies kann mit Hilfe der DML-Fehlerprotokollierung erfolgen, wenn Sie mit 10gR2 oder höher arbeiten.

Ein Beispiel:

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.

Dadurch wird eine Fehlerprotokolltabelle namens err$_my_table . Diese Tabelle wird gefüllt, indem Sie Ihrer Aktualisierungsanweisung eine Fehlerprotokollklausel hinzufügen:

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.

Vor 10gR2 können Sie die SAVE EXCEPTIONS-Klausel verwenden: http://rwijk.blogspot.com/2007/11/save-exceptions.html

Answer 2

Eine Lösung unter Verwendung der SAVE EXCEPTIONS-Klausel:

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.

Bei sehr großen Datensätzen möchten Sie wahrscheinlich nicht den PGA-Speicher sprengen, daher sollten Sie in diesem Fall die LIMIT-Klausel verwenden.

Answer 3

Versuchen Sie, Ihren Fehler auszugeben und sehen Sie, ob Sie die gewünschten Informationen erhalten. Zum Beispiel:

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

Answer 4

Um genauere Informationen darüber zu erhalten, wie die Ausführung zu der fraglichen Zeile gelangt ist, können Sie versuchen, die von diesen Funktionen zurückgegebene Ausgabe anzuzeigen:

• DBMS_UTILITY.format_error_stack :

  Formatiert den aktuellen Fehlerstapel. Dies kann in Exception-Handlern verwendet werden, um den vollständigen Fehlerstapel zu betrachten.

• DBMS_UTILITY.format_error_backtrace :

  Formatiert den Backtrace vom aktuellen Fehler bis zum Exception-Handler, in dem der Fehler abgefangen wurde. NULL-String wird zurückgegeben, wenn derzeit kein Fehler behandelt wird.

Answer 5

Versuchen Sie dies (nicht getestet):

    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;