ORACLE - DML Error Logging.

DML Error Logging is a new feature in ORACLE 10gR2.

By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. The LOG ERRORS clause in the DML statements enables the operations to complete regardless of errors.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements.

The following types of errors are handled by the error logging clause:

• Column values that are too large
• Constraint violations (NOT NULL, unique, referential, and check constraints).
• Errors raised during trigger execution
• Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
• Partition mapping errors

The following conditions cause the statement to fail and roll back without invoking the error logging capability:

• Violated deferred constraints
• Out-of-space errors
• Any direct-path INSERT operation (INSERT or MERGE) that raises a unique constraint or index violation
• Any UPDATE operation (UPDATE or MERGE) that raises a unique constraint or index violation

To do the DML operations with DML error logging:

1. Create an error logging table.
2. Execute the DML statement and include an error logging clause
3. Query the error logging table and take corrective action for the rows that generated errors.

§ Create an error logging table.

The error logging table consists of two parts.

1. A mandatory set of columns that describe the error.

2. An optional set of columns that contain data from the row that caused the error. The column names match the column names from the table being inserted into.

§ Mandatory Error Description Columns.

Column Name	Data Type	Description
ORA_ERR_NUMBER$	NUMBER	Oracle error number
ORA_ERR_MESG$	VARCHAR2(2000)	Oracle error message text
ORA_ERR_ROWID$	ROWID	Rowid of the row in error (for update and delete)
ORA_ERR_OPTYP$	VARCHAR2(2)	Type of operation: insert (I), update (U), delete (D)
ORA_ERR_TAG$	VARCHAR2(2000)	Value of the tag supplied by the user in the error logging clause

Example:

CREATE TABLE EXAM_RESULTS (

STUDENT_ID INTEGER ,
SUBJECT_ID VARCHAR2(5) NOT NULL,
MARKS NUMBER(6) NOT NULL,
REMARKS VARCHAR2(200));

ALTER TABLE EXAM_RESULTS
ADD CONSTRAINT EXAM_RESULTS_PK PRIMARY KEY (STUDENT_ID, SUBJECT_ID);

-- Create the error logging table

-- By using the DBMS_ERRLOG.CREATE_ERROR_LOG() procedure
-- we can automatically create the error logging table
SQL> EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('EXAM_RESULTS');

PL/SQL procedure successfully completed


SQL> DESCRIBE ERR$_EXAM_RESULTS;
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER Y
ORA_ERR_MESG$ VARCHAR2(2000) Y
ORA_ERR_ROWID$ UROWID(4000) Y
ORA_ERR_OPTYP$ VARCHAR2(2) Y
ORA_ERR_TAG$ VARCHAR2(2000) Y
STUDENT_ID VARCHAR2(4000) Y
SUBJECT_ID VARCHAR2(4000) Y
MARKS VARCHAR2(4000) Y
REMARKS VARCHAR2(4000) Y

Insert records with the DML Error Logging.

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1001, 'CS101', 75, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

1 row inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1002, 'CS101', 80, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

1 row inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1001, 'CS101', 85, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

0 rows inserted

SQL> INSERT INTO EXAM_RESULTS
2 VALUES(1002, 'CS101', 90, NULL)
3 LOG ERRORS INTO ERR$_EXAM_RESULTS REJECT LIMIT UNLIMITED;

0 rows inserted

In the above example two rows has the problem and they are moved to the error log table. The remaining records are moved to the source table successfully.

Oracle - Deferring Constraints.

Constraints can have the following attributes:

• DEFERRABLE or NOT DEFERRABLE
• INITIALLY DEFERRED or INITIALLY IMMEDIATE

You can defer checking constraints for validity until the end of the transaction. A constraint is deferred if the system checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to roll back.

Usage scenario: Company policy dictates that department number 40 should be changed to 45. Changing the DEPARTMENT_ID column affects employees assigned to this department. Therefore, you make the primary key and foreign keys deferrable and initially deferred. You update both department and employee information, and at the time of commit, all rows are validated.

Example: 1

CREATE TABLE tmp_dep (
dep_id NUMBER,
dep_name VARCHAR2(50));

ALTER TABLE tmp_dep
ADD CONSTRAINT tmp_dep_pk PRIMARY KEY (dep_id);

INSERT INTO tmp_dep
VALUES(40, 'Finance Department');

INSERT INTO tmp_dep
VALUES(41, 'Production Department' );

CREATE TABLE tmp_emp (
emp_id NUMBER,
emp_name VARCHAR2(50),
dep_id NUMBER);

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_pk PRIMARY KEY (emp_id);

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_fk FOREIGN KEY(dep_id)
REFERENCES tmp_dep(dep_id);

INSERT INTO tmp_emp
VALUES(1, 'Jophn', 40);

INSERT INTO tmp_emp
VALUES(2, 'Peter', 40);

When we will try to change the existing department id 40 to 45 we get the following error message.

UPDATE tmp_dep
SET dep_id = 45
WHERE dep_name = 'Finance Department'

ORA-02292: integrity constraint (IFSAPP.TMP_EMP_FK) violated - child record found

In this case we can achieve this by make constrains as DEFERRABALE’.

--drop the constraints
ALTER TABLE tmp_emp
DROP CONSTRAINT tmp_emp_fk;

ALTER TABLE tmp_dep
DROP CONSTRAINT tmp_dep_pk;

-- add the constraints with DEFERRABLE

ALTER TABLE tmp_dep
ADD CONSTRAINT tmp_dep_pk PRIMARY KEY (dep_id)
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE tmp_emp
ADD CONSTRAINT tmp_emp_fk FOREIGN KEY(dep_id)
REFERENCES tmp_dep(dep_id)
DEFERRABLE INITIALLY DEFERRED;

--Update the existing department id in the tmp_dep and tmp_emp tables.
UPDATE tmp_dep
SET dep_id = 45
WHERE dep_name = 'Finance Department';
1 row updated

UPDATE tmp_emp
SET dep_id=45
WHERE dep_id = 40;
2 rows updated

Difference Between INITIALLY DEFERRED and INITIALLY IMMEDIATE

A constraint that is defined as deferrable can be specified as either INITIALLY DEFERRED or INITIALLY IMMEDIATE. The INITIALLY IMMEDIATE clause is the default.

CREATE TABLE emp_new_sal (
salary NUMBER
CONSTRAINT sal_ck CHECK (salary > 100)
DEFERRABLE INITIALLY IMMEDIATE,
bonus NUMBER
CONSTRAINT bonus_ck CHECK (bonus > 0 )
DEFERRABLE INITIALLY DEFERRED );

• The sal_ck constraint is created as DEFERRABLE INITIALLY IMMEDIATE

• The bonus_ck constraint is created as DEFERRABLE INITIALLY DEFERRED

Example 1: Insert a row that violates sal_ck

INSERT INTO emp_new_sal VALUES(90,5);

ORA-02290: check constraint (IFSAPP.SAL_CK) violated

The sal_ck constraint defined as INITIALLY IMMEDIATE so the constraint verified immediately after the INSERT statement.

Example 2: Insert a row that violates bonus_ck

INSERT INTO emp_new_sal VALUES(110, -1);

1 row inserted

The bonus_ck constraint defined as INITIALLY DEFERRED so the row insertion is successful. Now commit the transaction.

COMMIT;

ORA-02091: transaction rolled back
ORA-02290: check constraint (IFSAPP.BONUS_CK) violated

The commit failed due to constraint violation. Therefore, at this point, the transaction is rolled back by the database.

Example 3: Set the DEFERRED status to all constraints

SET CONSTRAINTS ALL DEFERRED;

INSERT INTO emp_new_sal VALUES(90,5);

1 row inserted

COMMIT;

ORA-02091: transaction rolled back
ORA-02290: check constraint (IFSAPP.SAL_CK) violated

Now both constraints are set to DEFERRED status so sal_ck constraint is verified when the COMMIT happens.

Example4: Set the IMMEDIATE status to both the constraints

SET CONSTRAINT ALL IMMEDIATE;

INSERT INTO emp_new_sal VALUES(110, -1);

ORA-02290: check constraint (IFSAPP.BONUS_CK) violated

Now both constraints are set to IMMEDIATE status so bonus_ck constraint is verified immediate after the INSERT statement.

Note: If you create a table without specifying constraint deferability, then the constraint is checked immediately at the end of each statement.

CREATE TABLE newemp_details(
emp_id NUMBER,
emp_name VARCHAR2(20),
CONSTRAINT newemp_det_pk PRIMARY KEY(emp_id));

When you attempt to defer the newemp_det_pk constraint that is not deferrable, you observe the error message.

SET CONSTRAINT newemp_det_pk DEFERRED;

ORA-02447: cannot defer a constraint that is not deferrable

Quick Introduction to Oracle Views.

What is a View?

You can present logical subsets or combinations of data by creating views of tables. A view is a logical table based on a table or another view. A view contains no data of its own, but is like a window through which data from tables can be viewed or changed.

Advantages of Views

• Views restrict access to the data because it displays selected columns from the table.
• Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.
• Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.
• Views provide groups of users access to data according to their particular criteria.

Rules for Performing DML Operations on a View

• You cannot remove a row if the view contains the following:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword

• You cannot modify data in a view if it contains:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions

• You cannot add data through a view if the view includes:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions
• NOT NULL columns in the base tables that are not selected by the view

Example:1

Create a simple view.

CREATE TABLE emp (
employee_id INTEGER
CONSTRAINT employees_pk PRIMARY KEY,
manager_id INTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0));

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
1, NULL, 'James', 'Smith', 'CEO', 800000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
2, 1, 'Ron', 'Johnson', 'Sales Manager', 600000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
3, 2, 'Fred', 'Hobbs', 'Salesperson', 150000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
4, 2, 'Susan', 'Jones', 'Salesperson', 500000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
5, 3, 'Ran', 'Jill', 'Salesperson', 200000);

-- commit the transaction
COMMIT;

CREATE OR REPLACE VIEW tmp_view1 AS
SELECT employee_id, manager_id, first_name, salary
FROM emp;

SQL> describe tmp_view1;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
EMPLOYEE_ID INTEGER
MANAGER_ID INTEGER Y
FIRST_NAME VARCHAR2(10)
SALARY NUMBER(6) Y

Example: 2

Create a view with Group Function and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view2 AS
SELECT MAX(salary)AS Maximum_Salary
FROM emp;

SQL> DELETE tmp_view2;

DELETE tmp_view2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view2
2 SET Maximum_Salary = 912000;

UPDATE tmp_view2
SET Maximum_Salary = 912000

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view2
2 VALUES(988000);

INSERT INTO tmp_view2
VALUES(988000)

ORA-01732: data manipulation operation not legal on this view

Example: 3

Create a view with Group By and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view3 AS
SELECT manager_id, first_name, salary
FROM emp
GROUP BY manager_id, first_name, salary;

SQL> DELETE tmp_view3
2 WHERE manager_id = 2;

DELETE tmp_view3
WHERE manager_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view3
2 SET salary= salary + (0.1*salary);

UPDATE tmp_view3
SET salary= salary + (0.1*salary)

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view3
2 VALUES(8, 'Will Smith', 205444);

INSERT INTO tmp_view3
VALUES(8, 'Will Smith', 205444)

ORA-01400: cannot insert NULL into ("IFSAPP"."EMP"."EMPLOYEE_ID")

Example: 4

Create a view with DISTINCT and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view4 AS
SELECT DISTINCT(manager_id)
FROM emp;

SQL> DELETE tmp_view4
2 WHERE manager_id = 2;

DELETE tmp_view4
WHERE manager_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view4
2 SET manager_id= 4
3 WHERE manager_id IS NULL;

UPDATE tmp_view4
SET manager_id= 4
WHERE manager_id IS NULL

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view4
2 VALUES(9);

INSERT INTO tmp_view4
VALUES(9)

ORA-01732: data manipulation operation not legal on this view

Example 5:

Create a view with rownum pseudocolumn and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view5 AS
SELECT employee_id, first_name
FROM emp
WHERE rownum < 3;

SQL> DELETE tmp_view5
2 WHERE employee_id = 2;

DELETE tmp_view5
WHERE employee_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view5
2 SET first_name = 'Butner'
3 WHERE employee_id = 2;

UPDATE tmp_view5
SET first_name = 'Butner'
WHERE employee_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view5
2 VALUES(11, 'Adam');

INSERT INTO tmp_view5
VALUES(11, 'Adam')

ORA-01732: data manipulation operation not legal on this view

Oracle SQL Operators for WHERE Clauses

Operator	Meaning
=	Equals
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
<>	Not equal to
BETWEEN low AND high	Greater than or equal to the low value and less than or equal to the high value
EXISTS (subquery)	At least one row is present in the subquery
IN (list or subquery)	Equals one of the values in the list
LIKE pattern	Matches the pattern
NOT BETWEEN low AND high	Less than the low value or greater than the high value
NOT EXISTS (subquery)	No rows are present in the subquery
NOT IN (list or subquery)	Does not equal any of the values in the list
NOT LIKE pattern	Does not match the pattern
IS	Used only with NULL as the value
IS NOT	Used only with NULL as the value
= ALL (list or subquery)	Equal to every value in the list Note: If the list has at least two values, this test will fail.
!= ALL (list or subquery)	Does not equal even one of the values in the list Note: This is identical to the NOT IN operator.
<> or subquery)	Less than all of the values in the list
> ALL (list or subquery)	Greater than all of the values in the list
<= ALL (list or subquery)	Less than or equal to all of the values in the list
>= ALL (list or subquery)	Greater than or equal to all of the values in the list
= ANY (list or subquery)	Equal to at least one value in the list Note: This is identical to the IN operator.
<> subquery)	Less than at least one value in the list
> ANY (list or subquery)	Greater than at least one value in the list
<= ANY (list or subquery)	Less than or equal to at least one value in the list
>= ANY (list or subquery)	Greater than or equal to at least one value in the list
!= ANY (list or subquery)	Does not equal every one of the values in the list Note: If the list has at least two values, this test will fail.