Skip to Main Content
  • Questions
  • BUG 7538546 COMMIT is NOT DISABLED by command "ALTER SESSION DISABLE COMMIT IN PROCEDURE"

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Shushana.

Asked: September 01, 2022 - 10:19 pm UTC

Last updated: September 07, 2022 - 2:38 am UTC

Version: 19.12

Viewed 100+ times

You Asked

Hello,
We hit non-public Oracle BUG 7538546 that describes the scenario
where COMMIT is NOT DISABLED by command ""ALTER SESSION DISABLE COMMIT IN PROCEDURE" that runs before "AUTHID CURRENT_USER" command in the same package.

OracleSupport says this is not a priority, so there is NO ETA on the FIX.

Here is the question:
Is there know way of disabling/enabling commits where it's needed without rewriting entire application ?

Thank you and regards,
Shushana

TEST1 where COMMIT worked despite having
"ALTER SESSION DISABLE COMMIT IN PROCEDURE" before package with "AUTHID CURRENT_USER" :
=========================================================================================
SQL> CREATE TABLE test_tbl (USER_ID CHAR(15 BYTE) NOT NULL ) ;
Table created.

SQL> create or replace PACKAGE AAA_KD
  2  AUTHID CURRENT_USER
  3  AS
  4    PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED           IN          VARCHAR2);
  5  END AAA_KD;
  6
  7  /
Package created.

SQL> create or replace PACKAGE BODY AAA_KD
  2     AS
  3     PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED           IN          VARCHAR2)
  4         IS
  5     BEGIN
  6     delete test_tbl;
  7     insert into test_tbl select 'KEITH' from dual;
  8    commit;
  9    END COMMIT_TEST;
10  END AAA_KD;
11
12  /
Package body created.

SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE;
Session altered.

SQL> EXEC AAA_KD.COMMIT_TEST(NULL);
PL/SQL procedure successfully completed.

SQL> select * from  test_tbl;
USER_ID
---------------
KEITH


TEST2 where COMMIT produces EXPECTED ORA- error after running 
ALTER SESSION DISABLE COMMIT IN PROCEDURE; and  package with commented out line  "--AUTHID CURRENT_USER":
=========================================================================================================
SQL> create or replace PACKAGE AAA_KD
  2  --AUTHID CURRENT_USER
  3  AS
  4    PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED           IN          VARCHAR2);
  5  END AAA_KD;
  6
  7  /
Package created.

SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE;
Session altered.

SQL> EXEC AAA_KD.COMMIT_TEST(NULL);
BEGIN AAA_KD.COMMIT_TEST(NULL); END;

*
ERROR at line 1:
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "TMGRB9TS.AAA_KD", line 8
ORA-06512: at line 1


SQL> select * from  test_tbl;
USER_ID
---------------
KEITH



and Connor said...

I would push back to Support because you are on the supported release (19.12) and you've found a bug. Escalate and ask for it to be patched.

Sorry, I know of no way to block that commit without code changes.

Rating

  (1 rating)

Comments

The current transaction only

emaN, September 07, 2022 - 7:11 am UTC

You can start a transaction with a violation of deferred constraint on dummy table. Then commit throws an error. But after the rollback, the application will still be able to commit.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library