Table of Contents
This chapter briefly introduces the DBMS_ERRLOG package, and describes how to use the procedure of the package.
DBMS_ERRLOG provides a procedure for creating an error logging table.
When an error occurs in a DML, the DML fails and is rolled back. If error_logging_clause is specified in the DML statement, no error occurs, but the error detail and data are recorded in the error logging table.
Creates a table used for DML error logging.
Details about the CREATE_ERROR_LOG procedure are as follows:
Prototype
DBMS_ERRLOG.DECRYPT
(
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 DEFAULT NULL,
err_log_table_owner IN VARCHAR2 DEFAULT NULL,
err_log_table_space IN VARCHAR2 DEFAULT NULL,
skip_unsupported IN BOOLEAN DEFAULT FALSE
)
RETURN RAW
Parameter
| Parameter | Description |
|---|---|
| dml_table_name | Target DML table. |
| err_log_table_name | Error logging table. By default, ERR$_ is appended at the beginning of the name (dml_table_name). |
| err_log_table_owner | Name of the schema in which to create the error logging table. |
| err_log_table_space | Name of the tablespace in which to create the error logging table. |
| skip_unsupported | Only FALSE is currently supported. |
Example
SQL> create table p (a number primary key); Table 'P' created. SQL> insert into p values (1); 1 row inserted. SQL> insert into p values (2); 1 row inserted. SQL> insert into p values (3); 1 row inserted. SQL> insert into p values (4); 1 row inserted. SQL> create table f (a number references p(a)); Table 'F' created. SQL> insert into f values (1); 1 row inserted. SQL> insert into f values (3); 1 row inserted. SQL> exec dbms_errlog.create_error_log('f'); PSM completed. SQL> commit; Commit completed. SQL> insert into f (select 1 from dual union all select 5 from dual) log errors reject limit 1; 1 row inserted. SQL> select * from f; A ---------- 1 3 1 3 rows selected. SQL> select * from err$_f; TIB_ERR_NUMBER$ --------------- TIB_ERR_MESG$ -------------------------------------------------------------------------------- TIB_ERR_ROWID$ TIB_ERR_OPTYP$ ------------------ -------------------- TIB_ERR_TAG$ -------------------------------------------------------------------------------- A -------------------------------------------------------------------------------- -10008 INTEGRITY constraint ('SYS'.'SYS_CON25700497') violated: primary key not found. I 5 1 row selected.