Categories
dba sql

disable commit in procedure

There is an obscure syntax that prevents a procedure from issuing a commit


alter session DISABLE COMMIT IN PROCEDURE;

According to the doc, it prevents procedure from committing your data

Test case

SQL> alter session DISABLE COMMIT IN PROCEDURE
Session altered.
SQL> create table t(x number)
Table created.
SQL> create or replace procedure p is
begin
commit;
end;
Procedure created.
SQL> insert into t values (1)
1 row created.
SQL> exec p
BEGIN p; END;
Error at line 17
ORA-00034: cannot COMMIT in current PL/SQL session
ORA-06512: at "SCOTT.P", line 3
ORA-06512: at line 1

But some sys procedures may bypass this restriction

SQL> exec dbms_stats.gather_table_stats(user, 'T')
PL/SQL procedure successfully completed.
SQL> rollback
Rollback complete.
SQL> select * from t

X
----------
1

The row was silently committed.

Categories
dba sql sqlplus

On implicit commit

An explicit commit is when you issue a COMMIT statement

SQL> create table t(x number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

An implicit commit is when a commit is issued without your approval.

ex: AUTOCOMMIT (default is OFF)
SQL> set autoc on
SQL> insert into t values(1);

1 row created.

Commit complete.

ex: EXITCOMMIT (default is ON)
SQL> set autoc off exitc on
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/tiger
Connected.
SQL> select * from t;
X
----------
1

before / after a successful DDL statement
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t(x);

Index created.

SQL> rollback;

Rollback complete.

SQL> select * from t;
X
----------
1

Before / after an unsuccessful DDL statement, sometimes :
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t(blabla);
create index i on t(blabla)
*
ERROR at line 1:
ORA-00904: "BLABLA": invalid identifier

SQL> rollback;

Rollback complete.

SQL> select * from t;
X
----------
1

But not always :
SQL> truncate table t;

Table truncated.

SQL> insert into t values(1);

1 row created.

SQL> create index i on t();
create index i on t()
*
ERROR at line 1:
ORA-00936: missing expression

SQL> rollback;

Rollback complete.

SQL> select * from t;

no rows selected

In the last case, no DDL was executed, but in the case before that, the DDL was executed and failed.

If you want to commit, use COMMIT 🙂

Categories
sql

committing transaction

When and how do you commit a transaction?
SQL> insert into lsc_t(x) values(1); ... (1) not committed
SQL> commit; ... (1) committed

SQL> insert into lsc_t(x) values(2); ... (2) not committed
SQL> alter table lsc_t disable primary key; ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3); ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4); ... (4) not committed
SQL> disc ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed after before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?


SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
PID TTY TIME CMD
13903 pts/33 0:00 ksh
22384 pts/33 0:00 sqlplus

SQL> !kill 22384
Terminated

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
– A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
– A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
– A user disconnects from Oracle Database. The current transaction is committed.
– A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION

Please read On Implicit Commit