Allow Commit/Rollback in PRAGMA AUTONOMOUS_TRANSACTION

Question

At the application level, we enforce "ALTER SESSION DISABLE COMMIT IN PROCEDURE" to restrict commits/rollbacks inside procedures, but this also blocks Pragma Autonomous Transactions as well.

Need a way (from the application side, no PL/SQL changes) to allow commits/rollbacks inside Pragma Autonomous Transactions while keeping other commits disabled.

Question: How can we achieve this restriction at the application level without affecting autonomous transactions?

Answer 1

I don't think you can do that.

What problem are you trying to solve, actually?

• procedures shouldn't COMMIT at all; that should be left to the caller to decide anyway

  • which means that you could disable commit/rollback to isolate/find procedures that ARE committing (they will fail) and fix them (i.e. remove COMMIT)

  • on the other hand, perhaps you'd rather search for commits in code, e.g.

    select * from user_source where lower(text) like '%commit%';
    

• where & why do you use autonomous transaction? It should be used for logging purposes only, for situations where certain code fails, you call a logging procedure which is an autonomous transaction so that it could insert logging information - procedure that raised the error, position within that procedure, statement you ran, error you got - and commit that insert into the log table. Don't forget to RAISE afterwards, if necessary

  • why would you NOT want to RAISE? In a case you're running some code in a loop and want to log errors you get, but also move on with the next loop iteration and later check what went wrong

• if you're misusing autonomous transaction - maybe the most common situation is "solving" mutating table error - then give up on it, it is the wrong way to do it. There are techniques to overcome such an error (a compound trigger, or a package (in older Oracle database versions))

• depending on what you're doing, perhaps you could call a procedure (which is committing) via DBMS_SCHEDULER. Although that is possible, I'm not sure it is the right way to do it; probably depends on what you do

Anyway: if you alter session and disable commits, that's how it is - commits are disabled. "No PL/SQL changes"? There's no such thing as a free lunch.