Search from 700 + Posts

Jan 16, 2013

ORA-02064: distributed operation not supported


ORA-02064: distributed operation not supported is raised by Oracle database if you are trying to access a database API via dblink and api has Commit or rollback statement in it .

One of the option to avoid this type of error is to include PRAGMA AUTONOMOUS_TRANSACTION  in your code

BEFORE
Example API Code
procedure xxyy (... .... ..)
variable declaration
BEGIN
----
---
--
IF Success THEN
   COMMIT;
ELSE
   ROLLBACK;
EXCEPTION
------
------
END

if you try to access above code via db link , system will raise ORA-02064 error , and to fix it include PRAGMA AUTONOMOUS_TRANSACTION in your code as shown below


AFTER


procedure xxyy (... .... ..)
variable declaration
PRAGMA AUTONOMOUS_TRANSACTION;


BEGIN
----
---
--
IF Success THEN
   COMMIT;
ELSE
   ROLLBACK;
EXCEPTION
------
------
END

No comments:

Post a Comment