LOG ERRORS Oracle 10gR2 : don't rollback just for 1 single exception !

Die Softwareentwickler mögen Schleife und Cursors über alles, und finden desshalb die unwahrscheinlichsten Rechtfertigungen um die globale DML query zu vermeiden.

Unter denen gibt's die Handlung der Fehlerfällen :

"Neee, die ganze Anfrage darf nicht Rollbacked werden, nur wegen einer verdammten Zeile"

Aber seit 10gR2 gilt dieses Argument (fast) nicht mehr, denn es gibt jetzt die ERROR LOGGING !

Versuchen wir's mal mit verschiedenen Fehler Gründen...

1) Die Testtabelle :

 CREATE TABLE testlog (

 id NUMBER UNIQUE,

 n NUMBER CHECK(n > 0),

 c VARCHAR2(10) NOT NULL);

2) Die Errorlogtabelle :

EXEC dbms_errlog.create_error_log('TESTLOG', 'TESTERR')

3) Einfügen

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 2 THEN 1 ELSE level END, --die zweite Zeile wird unique contraint verletzen

  3         CASE level WHEN 4 THEN -1 ELSE level END, --die vierte verletzt die positiv check bedingung

  4         CASE level WHEN 6 THEN null WHEN 8 THEN lpad('x', 12, 'x') ELSE 'd' END --die sechte die NOT NULL bedingung und die achte überschreitet die maximale Länge

  5  FROM DUAL

  6  CONNECT BY level <= 12

  7  LOG ERRORS INTO TESTERR ('instest') REJECT LIMIT UNLIMITED;

8 ligne(s) créée(s).

SQL> SELECT *

  2  FROM testlog

  3  /

  ID    N C

---- ---- --------------------

   1    1 d

   3    3 d

   5    5 d

   7    7 d

   9    9 d

  10   10 d

  11   11 d

  12   12 d

=> 8 der 12 zeilen sind erfolgreich hinzufügen worden !

SQL> SELECT * FROM testerr;

 

Und natürlich klappt  es auch mit UPDATE, MERGE UND DELETE !

SQL> MERGE INTO testlog a

  2  USING (SELECT level l,

  3             CASE level WHEN 4 THEN -1 ELSE level + 1 END n,

  4             CASE level WHEN 5 THEN null ELSE 'c' END c

  5         FROM DUAL CONNECT BY LEVEL <= 13) t

  6  ON (a.id = t.l)

  7  WHEN MATCHED THEN UPDATE SET a.n = t.n, a.c = t.c

  8  WHEN NOT MATCHED THEN INSERT (a.id, a.n, a.c) VALUES(t.l, t.n, t.c)

  9  LOG ERRORS INTO TESTERR('testmerge') REJECT LIMIT UNLIMITED;

11 lignes fusionnées.

SQL> SELECT * FROM testlog;

  ID    N C

---- ---- --------------------

   1    2 c

   3    4 c

   5    5 d

   7    8 c

   9   10 c

  10   11 c

  11   12 c

  12   13 c

   2    3 c

   6    7 c

   8    9 c

  13   14 c

SQL> SELECT ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_TAG$

  2  FROM testerr;

4) Trotzdem ein Paar Schertze

Manchmal werden die unique contraints nur "am Ende" überprüft : in diese Fälle scheitert die ganze Anfrage.

Zum beispiel den direct path load :

SQL> INSERT INTO testlog

  2  SELECT /*+APPEND*/ CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

INSERT INTO testlog

*

ERREUR à la ligne 1 :

ORA-00001: unique constraint (EDGE_ADM.SYS_C00356572) violated

Ohne den append hint klappt's prima :

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

1 ligne créée.