Local Table to Modify
CREATE TABLE employees AS SELECT * FROM hr.employeesTable 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 |
|---|