Local Table to Modify
CREATE TABLE employees AS SELECT * FROM hr.employees
Table created.
Create the Error Log Table
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'EMPLOYEES');
END;
Statement processed.
Show Columns of Error Log Table
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = 'ERR$_EMPLOYEES'
ORDER BY COLUMN_ID
COLUMN_NAME | DATA_TYPE |
---|---|
ORA_ERR_NUMBER$ | NUMBER |
ORA_ERR_MESG$ | VARCHAR2 |
ORA_ERR_ROWID$ | UROWID |
ORA_ERR_OPTYP$ | VARCHAR2 |
ORA_ERR_TAG$ | VARCHAR2 |
EMPLOYEE_ID | VARCHAR2 |
FIRST_NAME | VARCHAR2 |
LAST_NAME | VARCHAR2 |
VARCHAR2 | |
PHONE_NUMBER | VARCHAR2 |
HIRE_DATE | VARCHAR2 |
JOB_ID | VARCHAR2 |
SALARY | VARCHAR2 |
COMMISSION_PCT | VARCHAR2 |
MANAGER_ID | VARCHAR2 |
DEPARTMENT_ID | VARCHAR2 |
All or Nothing - Without LOG ERRORS
DECLARE
l_count PLS_INTEGER;
BEGIN
SELECT COUNT ( * )
INTO l_count
FROM employees
WHERE salary > 24000;
DBMS_OUTPUT.put_line ('Before ' || l_count);
UPDATE employees
SET salary = salary * 200;
SELECT COUNT ( * )
INTO l_count
FROM employees
WHERE salary > 24000;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
SELECT COUNT ( * )
INTO l_count
FROM employees
WHERE salary > 24000;
DBMS_OUTPUT.put_line ('After ' || l_count);
END;
Statement processed.
Before 0
ORA-01438: value larger than specified precision allowed for this column
After 0
Suppressing Row-Level Errors
DECLARE
l_count PLS_INTEGER;
BEGIN
SELECT COUNT ( * )
INTO l_count
FROM employees
WHERE salary > 24000;
DBMS_OUTPUT.put_line ('Before ' || l_count);
UPDATE employees
SET salary = salary * 200
LOG ERRORS
INTO ERR$_EMPLOYEES (substr (last_name, 1, 20)) REJECT LIMIT UNLIMITED;
DBMS_OUTPUT.put_line ('After - SQL%ROWCOUNT ' || SQL%ROWCOUNT);
SELECT COUNT ( * )
INTO l_count
FROM employees
WHERE salary > 24000;
DBMS_OUTPUT.put_line ('After - Count in Table ' || l_count);
ROLLBACK;
END;
Statement processed.
Before 0
After - SQL%ROWCOUNT 49
After - Count in Table 49
SELECT COUNT ( * ) "Number of Failures"
FROM err$_employees
Number of Failures |
---|
58 |
Check the Error Log Table!
SELECT ora_err_number$, ora_err_mesg$, ora_err_rowid$, ora_err_tag$, last_name
FROM err$_employees
WHERE ROWNUM < 10
ORA_ERR_NUMBER$ | ORA_ERR_MESG$ | ORA_ERR_ROWID$ | ORA_ERR_TAG$ | LAST_NAME |
---|---|---|---|---|
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAA | King | King |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAB | Kochhar | Kochhar |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAC | De Haan | De Haan |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAD | Hunold | Hunold |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAE | Ernst | Ernst |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAI | Greenberg | Greenberg |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAJ | Faviet | Faviet |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAK | Chen | Chen |
1438 | ORA-01438: value larger than specified precision allowed for this column | ABZf4JAAkAAABYbAAL | Sciarra | Sciarra |
Clean Up the Error Log Table
BEGIN
DELETE FROM err$_employees;
COMMIT;
END;
Statement processed.
Specify Limit on Rejections (Errors)
BEGIN
UPDATE employees
SET first_name = first_name || first_name || first_name
LOG ERRORS REJECT LIMIT 10;
ROLLBACK;
END;
ORA-12899: value too large for column "SQL_DEPRRIYITCLBHRKZOTIYAWXHA"."EMPLOYEES"."FIRST_NAME" (actual: 21, maximum: 20) ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-12899
SELECT 'Number of errors = ' || COUNT ( * )
FROM err$_employees
'NUMBEROFERRORS='||COUNT(*) |
---|
Number of errors = 11 |