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.