CODEWITHSUNDEEP
oracle-databaseeventsplsqlcommit
Vilius Gaidelis
Vilius Gaidelis

Reputation: 450

How to catch the event where in PL/SQL code the first commit occurs in Oracle?

Is the way in Oracle automatically catch the event where in PL/SQL code the first commit occurs?

I have a big problem with PL/SQL procedure which should not do any commits but it does a commit somewhere.

This procedure runs a huge amount of pl/sql code, a lot of dynamic pl/sql code, some packages are wrapped and some packages are not granted for debug.

Maybe someone had it a similar problem and can help me?

Upvotes: 2

Views: 1216

Answers (2)

Vilius Gaidelis
Vilius Gaidelis

Reputation: 450

I found the answer:

ALTER SESSION DISABLE COMMIT IN PROCEDURE

This command disables the possibility of commit and, what is most important for me the oracle error message shows the exact place where in pl/sql code the commit is.

SQL> create or replace procedure tst_commit is
  2  begin
  3    dbms_output.put_line('before commit');
  4    COMMIT;
  5    dbms_output.put_line('after commit');
  6  end tst_commit;
  7  /
Procedure created

SQL> BEGIN
  2    EXECUTE IMMEDIATE 'ALTER SESSION DISABLE COMMIT IN PROCEDURE';
  3    INSERT INTO viliusg.log VALUES(SYSDATE,'test commit');
  4    tst_commit;
  5     dbms_output.put_line('THE END');
  6  END;
  7  /
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION DISABLE COMMIT IN PROCEDURE';
  INSERT INTO viliusg.log VALUES(SYSDATE,'test commit');
  tst_commit;
    dbms_output.put_line('THE END');
END;
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "FORPOST.TST_COMMIT", line 4
ORA-06512: at line 4

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

Your PL/SQL block will never COMMIT the changes until and unless you have explicitly mentioned COMMIT.

However, you might have DDL statements executed as dynamic SQL which might be going for an implicit commit. Search for the commit in the code.

You could look into USER_SOURCE.

SELECT * 
   FROM USER_SOURCE
  WHERE NAME = '<PROCEDURE NAME IN UPPER CASE>'
  AND   TYPE = 'PROCEDURE'
  AND   UPPER(TEXT) LIKE '%COMMIT%'

Update

To find the DDL statements, you could look for keywords like CREATE, DROP, ALTER

SELECT * 
   FROM USER_SOURCE
  WHERE NAME = '<PROCEDURE NAME IN UPPER CASE>'
  AND   TYPE = 'PROCEDURE'
  AND   UPPER(TEXT) LIKE '%CREATE%'
  OR    UPPER(TEXT) LIKE '%DROP%'
  OR    UPPER(TEXT) LIKE '%ALTER%'

Upvotes: 1

Related Questions