Identify illegal commit in oracle procedure

Question

I want to find out, if there is an illegal commit in my Oracle PL/SQL code, perhaps nested deeply. Thus, I executed "alter session disable commit in procedure". With this, an error will be raised if that happens.

But.. It can be that there are savepoints defined in the code, and perhaps it is rolled back to these. This is ok, because that will not create inconsistent data.

Unfortunately, Oracle seems not to allow any transaction control statements when "disable commit in procedure" is set.

So, I am looking for a way to forbid commits in the code, but allow useful transaction control.

Answer 1

What do you call an "illegal commit"?

Perhaps you'd want to query user_source and see which parts of code contain any reference to commit, review them and do something:

• either leave them as they are because you really need them, for example in autonomous transaction procedures, or
• remove (comment?) them because you don't need/want them

Something like this:

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

NAME             TYPE               LINE TEXT
---------------- ------------ ---------- ----------------------------------------
PKG_GENERAL      PACKAGE BODY        297   -- transaction ended without commit or rollback
PKG_GENERAL      PACKAGE BODY        320       COMMIT;
PKG_LOG          PACKAGE BODY         45       COMMIT;
PKG_LOG          PACKAGE BODY        134       COMMIT;
PKG_MAIL         PACKAGE BODY        219       COMMIT;

Answer 2

You can prevent commits with a deferrable constraint. Before running the targeted procedure, create a row that violates a deferrable constraint, and after the procedure is complete, remove that row to enable commits again. If there are any commits inside the procedure, the deferrable constraint will fail, the transaction will roll back, and Oracle will raise an exception that points to the line of code that caused a commit.

First, create a table with a deferrable constraint that is easy to violate:

create table cannot_have_rows
(
    a number,
    constraint cannot_have_rows_ck check(a is null and a is not null) deferrable initially deferred
);

comment on table cannot_have_rows is 'This table exists only to prevent unwanted commits.';

Below is an example of how to disable and enable commits by using the table CANNOT_HAVE_ROWS.

declare
    procedure disable_commits is
    begin
        insert into cannot_have_rows values(1);
    end disable_commits;

    procedure enable_commits is
    begin
        delete from cannot_have_rows;
    end enable_commits;

    procedure prevent_commits_in_this_code is
    begin
        -- Most transaction control statements still work.
        savepoint savepoint1;
        insert into test1 values(1);
        rollback to savepoint1;

        --This line will raise an exception.
        commit;
    end;
begin
    disable_commits;
    prevent_commits_in_this_code;
    enable_commits;
exception when others then
    --You probably always want to re-enable commits at the end, even if there's an error.
    enable_commits;
    raise;
end;
/

The above anonymous block will generate this error:

ORA-02091: transaction rolled back
ORA-06512: at line 29
ORA-02290: check constraint (JHELLER.CANNOT_HAVE_ROWS_CK) violated
ORA-06512: at line 20
ORA-06512: at line 24

The above code isn't a great solution, but in many code bases it is nearly impossible to guarantee that something doesn't happen. Oracle code paths can be ridiculously difficult to follow when you consider CURRENT_SCHEMA, SYNONYMS, overriding object names (for example, imagine creating a custom package named DBMS_OUTPUT), dynamic SQL, code run indirectly (like triggers, jobs, scheduler jobs, advanced rewrite directives and other plan control features), etc. I once spent an entire week reviewing code to be 100% sure that there were no unwanted commits. There's not always an easy solution to a problem that is equivalent to the halting problem.