|
When running in sql_mode=ORACLE, MariaDB will understand an optional LOG ERRORS clause with this syntax:
LOG ERRORS
|
[INTO [schema.]table]
|
[('simple_expression')]
|
[REJECT LIMIT integer|UNLIMITED]
|
Example:
DELETE FROM t1
|
LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1;
|
|
INSERT INTO t1 (a,b,c) SELECT a,b,c FROM t2
|
LOG ERRORS INTO ERROR_LOG_DMLEL REJECT LIMIT 1
|
LOG ERRORS is used for two purposes:
1. For actual logging of errors
2. To change transaction error handling behavior. By default, when a DML statement fails on some row (e.g. due to a CONSTRAINT check, the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The REJECT LIMIT clause specifies the maximum number of errors before the statement fails.
If the INTO clause is omitted, then logging is done to the table ERR$_table, where table is the name of the subject table truncated to 25 characters.
The log table must be created, either manually, or using a special stored procedure DBMS_ERRLOG.create_error_log. The log table have the following mandatory columns:
CREATE TABLE ERR$_T1
|
(
|
ORA_ERR_NUMBER$ NUMBER,
|
ORA_ERR_MESG$ VARCHAR2(2000),
|
ORA_ERR_ROWID$ ROWID,
|
ORA_ERR_OPTYP$ VARCHAR2(2),
|
ORA_ERR_TAG$ VARCHAR2(2000)
|
);
|
If the REJECT clause is omitted, then LIMIT is set to 0 by default, which means the traditional transaction behavior, i.e. rollback on errors.
Example:
Preparations
DROP TABLE err$_t1;
|
BEGIN
|
DBMS_ERRLOG.create_error_log (dml_table_name => 't1');
|
END;
|
/
|
DROP TABLE t1;
|
CREATE TABLE t1 (a INT PRIMARY KEY);
|
INSERT INTO t1 VALUES (10);
|
Testing LOG ERRORS REJECT LIMIT 1
Now I insert a row which would normally raise a "err_dup_key" exception:
INSERT INTO t1 VALUES (10) LOG ERRORS REJECT LIMIT 1;
|
Notice, no exceptions were raised, and a record in err$_t1 was created instead:
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
|
ORA_ERR_NUMBER$
|
---------------
|
ORA_ERR_MESG$
|
--------------------------------------------------------------------------------
|
1
|
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
|
Testing LOG ERRORS with no REJECT LIMIT clause.
DELETE FROM err$_t1;
|
INSERT INTO t1 VALUES (10) LOG ERRORS;
|
ERROR at line 1:
|
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
|
SELECT ORA_ERR_NUMBER$,ORA_ERR_MESG$ FROM err$_t1;
|
ORA_ERR_NUMBER$
|
---------------
|
ORA_ERR_MESG$
|
--------------------------------------------------------------------------------
|
1
|
ORA-00001: unique constraint (SYSTEM.SYS_C0071433) violated
|
|
[MDEV-10589]