Autonomous Transactions (10g)

From NazimWIKI
Jump to navigation Jump to search

Requirement

The task at hand was to create a web-based stored procedure which would enable people from our Security team to add database users themselves, rather than forward on calls to the ever-busy DBAs.


So I started out by creating a mod/plsql stored procedure on an admin database, which would use database links to connect to the target database where the account needs to be created.


Problem Encountered:

Oracle does not allow DDL commands to run across a dblink.


Solution 1:

Use the dblink to execute another stored procedure on the target, which would run the DDL commands.


Pretty straight forward at this point. Then comes the kicker ...


I needed to add an OUT parameter to the target procedure, so I could pass a message back to the calling source procedure advising the success/failure of the procedure at the target.


Problem Encountered:

Oracle Bug: where a combination of dblink, OUT parameter and DDLs are NOT allowed. According to MetaLink this would be rectified in version 11, but was hardly of any use to me at this point.


Solution 2:

Autonomous Transactions.

Stumbled upon the use of Autonomous Transactions, which as I understand them, allow procedures to execute autonomously from the calling procedure. By running something like:

execute immediate 'DECLARE PRAGMA AUTONOMOUS TRANSACTION BEGIN END;'; 

I was able to have my cake and eat it too!