ORA-02067: transaction or savepoint rollback required

Error Description:

Transaction or savepoint rollback required

Error Cause:

A failure (typically a trigger or stored procedure with multiple remote updates) occurred such that the all-or-nothing execution of a previous Oracle call cannot be guaranteed.

Action:

Rollback to a previous savepoint or rollback the transaction and resubmit.

Example case:

Here in the student table have an insert trigger. The trigger fire when anything insert in student table and copy inserted information to other schema.

When Insert Trigger is Enable:

SQL> Insert into STUDENT(name,roll,department) values('Tom',12123, 'Business');

Insert into STUDENT

*

ERROR at line 1:

ORA-02055: distributed update operation failed; rollback required

ORA-02291: integrity constraint (FK_STUDENT_DEPARTMENT_NAME)

violated - parent key not found

Now any query will give the ORA-02067 error

SQL> select sysdate from dual;

select sysdate from dual

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02067: transaction or savepoint rollback required

ORA-02067: transaction or savepoint rollback required

SQL> rollback;

Rollback complete.

Now everything will be works fine again.

SQL> select sysdate from dual;

SYSDATE

-------------------

2013-12-20 11:28:43

When Insert Trigger is Disable:

SQL> Insert into STUDENT(name,roll,department) values('Tom',12123, 'Business');

ERROR at line 1:

ORA-02291: integrity constraint (FK_STUDENT_DEPARTMENT_NAME)

violated - parent key not found

No ORA-02067 error

SQL> select sysdate from dual;

SYSDATE

-------------------

2013-12-20 13:00:47

In the trigger we could catch exception for ORA-02055 and rollback but the problem is a trigger cannot contain a commit or rollback statement, however by using autonomous transaction we can overcome this limitation but it’s consider bad practice but it is possible.