What is DML Error Logging in Oracle?
How to handle Exceptions if any error occurs in
between DML operations?
How to handle Exceptions with MERGE Statement?
We have been using Exception Handling clause to track any Error inside
loop and at the end of any DML statements.
Let’s suppose we are dealing with DML statements processing number of
records and there are Exceptions in processing some of the Records- Then if the
statement fails the whole Process get rolled back (By Default) regardless of
how many rows were processed successfully before the error was detected.
In such situations, the only work around was to process each row
individually by tracking Error using BULK Collect FORALL with SAVE EXCEPTION
clause.
Now, Error Logging feature
has been introduced to solve this problem. Adding the appropriate LOG
ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements
enables the operations to complete, regardless of errors. It captures the Error
Logs and process rest of the Records which are good to go.
Syntax
The syntax for the error logging clause is the same for INSERT, UPDATE,
MERGE and DELETE statements.
LOG ERRORS [INTO [SCHEMA.]TABLE]
[('SIMPLE_EXPRESSION')] [REJECT LIMIT INTEGER|UNLIMITED]
INTO CLAUSE: The optional INTO CLAUSE allows you to specify the name of the error logging
table. If you omit this clause, the first 25 characters of the base table name
are used along with the "ERR$_" Prefix.
SIMPLE_EXPRESSION: The SIMPLE_EXPRESSION is used to specify
a tag that makes the errors easier to identify. This might be a string or any
function whose result is converted to a string.
REJECT LIMIT: The REJECT LIMIT is used to specify
the maximum number of errors before the statement fails. The default value is 0
and the maximum values is the keyword UNLIMITED. For parallel DML operations, the reject limit is applied to each
parallel server.
Let’s see how it works with DML
Operations.
ERROR LOGGING While Processing INSERT
When we built the sample schema we
noted that the CODE column is optional in the SOURCE table,
but mandatory in the DEST table. When we populated the SOURCE table
we set the code to NULL for two of the rows. If we try to copy the data from
the SOURCE table to the DEST table we get the following
result.
SQL> INSERT
INTO DEST SELECT * FROM SOURCE;
SELECT *
*
ERROR at line 2:
ORA-01400: cannot
insert NULL into ("TEST"."DEST"."CODE")
The failure causes the whole insert to
roll back, regardless of how many rows were inserted successfully. Adding the
DML error logging clause allows us to complete the insert of the valid rows.
SQL> INSERT
INTO DEST SELECT * FROM SOURCE;
LOG ERRORS INTO
ERR$_DEST ('INSERT') REJECT LIMIT UNLIMITED;
99998 rows
created.
The rows that failed during the insert are stored
in the ERR$_DEST table, along with the reason for the failure.
SQL> SELECT ORA_ERR_NUMBER$,
ORA_ERR_MESG$ FROM ERR$_DEST
WHERE ORA_ERR_TAG$
= 'INSERT';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------
1400 ORA-01400: cannot insert NULL into
("TEST"."DEST"."CODE")
1400 ORA-01400:
cannot insert NULL into ("TEST"."DEST"."CODE")
2 rows selected.
ERROR LOGGING While Processing UPDATE
The following code
attempts to update the CODE column for 10 rows, setting it to itself
for 8 rows and to the value NULL for 2 rows.
SQL> UPDATE
DEST SET CODE = DECODE(ID, 9, NULL, 10, NULL, CODE)
WHERE ID
BETWEEN 1 AND 10;
*
ERROR at line 2:
ORA-01407: cannot
update ("TEST"."DEST"."CODE") to NULL
As expected, the
statement fails because the CODE column is mandatory. Adding the DML
error logging clause allows us to complete the update of the valid rows.
SQL> UPDATE DEST
SET CODE = DECODE(ID, 9, NULL, 10, NULL, CODE)
WHERE ID
BETWEEN 1 AND 10
LOG ERRORS INTO
ERR$_DEST ('UPDATE') REJECT LIMIT UNLIMITED;
8 rows updated.
The rows that
failed during the update are stored in the ERR$_DEST table, along
with the reason for the failure.
SQL> SELECT ORA_ERR_NUMBER$,
ORA_ERR_MESG$ FROM ERR$_DEST
WHERE ORA_ERR_TAG$
= 'UPDATE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------
1400 ORA-01400: cannot insert NULL into
("TEST"."DEST"."CODE")
1400 ORA-01400:
cannot insert NULL into ("TEST"."DEST"."CODE")
2 rows selected.
ERROR LOGGING While Processing DELETE
The DEST_CHILD table
has a foreign key to the DEST table, so if we add some data to it
would expect an error if we tried to delete the parent rows from the DEST table.
SQL> INSERT
INTO DEST_CHILD (ID, DEST_ID) VALUES (1, 100);
SQL> INSERT
INTO DEST_CHILD (ID, DEST_ID)VALUES (2,
101);
With the child
data in place we ca attempt to delete th data from the DEST table.
SQL> DELETE
FROM DEST;
*
ERROR at line 1:
ORA-02292:
integrity constraint (TEST.DEST_CHILD_DEST_FK) violated - child record found
As expected, the
delete operation fails. Adding the DML error logging clause allows the delete operation
to complete.
SQL> DELETE FROM DEST
LOG ERRORS INTO ERR$_DEST ('DELETE')
REJECT LIMIT UNLIMITED;
99996 rows
deleted.
The rows that
failed during the delete operation are stored in the ERR$_DEST table,
along with the reason for the failure.
SQL> SELECT ORA_ERR_NUMBER$,
ORA_ERR_MESG$ FROM ERR$_DEST
WHERE ORA_ERR_TAG$
= 'UPDATE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
---------------
---------------------------------------------------------------------
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK)
violated - child record found
2292 ORA-02292: integrity constraint (TEST.DEST_CHILD_DEST_FK)
violated - child record found
2 rows selected.
ERROR LOGGING While Processing MERGE Statement
The following code
deletes some of the rows from the DEST table, then attempts to merge
the data from the SOURCE table into the DEST table.
SQL> DELETE
FROM DEST WHERE ID > 50000;
SQL> MERGE INTO
DEST A USING SOURCE B
ON (A.ID = B.ID)
WHEN MATCHED THEN
UPDATE
SET A.CODE= B.CODE, A.DESCRIPTION = B.DESCRIPTION
WHEN NOT MATCHED THEN
INSERT (ID, CODE, DESCRIPTION)
VALUES (B.ID, B.CODE, B.DESCRIPTION);
*
ERROR at line 9:
ORA-01400: cannot
insert NULL into ("TEST"."DEST"."CODE")
As expected, the
merge operation fails and rolls back. Adding the DML error logging clause
allows the merge operation to complete.
SQL> MERGE INTO
DEST A USING SOURCE B
ON (A.ID = B.ID)
WHEN MATCHED THEN
UPDATE
SET A.CODE= B.CODE, A.DESCRIPTION = B.DESCRIPTION
WHEN NOT MATCHED THEN
INSERT (ID, CODE, DESCRIPTION)
VALUES (B.ID, B.CODE, B.DESCRIPTION))
LOG ERRORS INTO ERR$_DEST ('MERGE') REJECT LIMIT
UNLIMITED;
99998 rows merged.
The rows that
failed during the update are stored in the ERR$_DEST table, along
with the reason for the failure.
SQL> SELECT ORA_ERR_NUMBER$,
ORA_ERR_MESG$ FROM ERR$_DEST
WHERE ORA_ERR_TAG$
= 'MERGE';
ORA_ERR_NUMBER$ ORA_ERR_MESG$
------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------
1400 ORA-01400: cannot insert NULL into
("TEST"."DEST"."CODE")
1400 ORA-01400:
cannot insert NULL into ("TEST"."DEST"."CODE")
2 rows selected.
Source: https://oracle-base.com
This comment has been removed by a blog administrator.
ReplyDelete