Search code examples
sqloracleplsqlcommitrollback

Force rollback in case of an implicit commit

Is there a way in SQL or PLSQL that makes sure that nothing is committed? Since, sometimes a function/procedure is called and the consequences are not known. For example, it can trigger an implicit commit. Is there a way to prevent that?

Solution

  • I am giving example in Oracle Database

    This is for the SQL statements DML.

    SQL> set transaction read only;

Transaction set.

SQL> update t set t=14;
update t set t=14
       *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
transaction

    This for calling procedure which has commits in it.

      SQL>alter session DISABLE COMMIT IN PROCEDURE ;
  SQL>exec procedureHavingCommit(10);
    BEGIN procedureHavingCommit(10); END;

    *
    ERROR at line 1:
    ORA-00034: cannot COMMIT in current PL/SQL session
    ORA-06512: at "ND210.DRMOP_UTIL", line 332
    ORA-06512: at "ND210.DRMOP_UTIL", line 1664
    ORA-00034: cannot COMMIT in current PL/SQL session
    ORA-06512: at line 1