PL SQL Auto Commit on execution

Question

I am very new to the PL/SQL programming. I tried to write a pl/sql procedure with some DML Statements(insert) inside the code. I am not doing any explicit commit after performing insert operations in the pl/sql code. But the transaction is getting commited after executing pl/sql procedure.

is this the default behaviour?

How can i control this?

Highly dependent on the client you are using. Which is that? Sql*Plus? Oracle DB on its own does not have auto commit for DML — Vrashabh Irde, Aug 04 '13 at 11:58
I am using sql* plus and perl dbi for running the pl/sql procedures — Mohan, Aug 04 '13 at 12:01

score 6 · Accepted Answer · answered Aug 04 '13 at 12:04

6

DML statements (INSERT/DELETE/UPDATE/MERGE) don't do an auto commit in PL/SQL. DDL statements do commit (ALTER/CREATE etc) and this will happen even if something failed. If you're running EXECUTE IMMEDIATE like dynamic statement that runs a DDL, this will also commit your transaction. And its been like that [and will remain] since 2000

Client interfaces like SQL*Plus have an auto commit feature that can be turned off/on , look for it in the client documentations. Something like

SET AUTOCOMMIT OFF

You can see the current status of this variable

SHOW AUTCOMMIT

and that will tell you whether its on/off .

Go through this for more variations of autocommit

answered Aug 04 '13 at 12:04

Vrashabh Irde

14,129
6
51
103

So If we have any DML statements inside pl/sql procedure and if i am not explicitly commiting it inside the pl/sql procedure, the transactions wont be commited. And all the transactions will be rolled back after the procedure execution is completed. Is it correct? – Mohan Aug 04 '13 at 13:00
1

Yes, but make sure the auto commit feature of SQL*plus is off. Also there are a few DBMS packages that auto commit. So for instance if you are gathering statistics using DBMS_STATS after running the procedure then there is an implicit commit within that package. Also google savepoints, autonomous_transaction and look at http://stackoverflow.com/questions/11966020/begin-end-block-atomic-transactions-in-pl-sql for more on this topic. But the best practice is to issue commits through a calling function for the proc when it is successful and rollback on exceptions. – Vrashabh Irde Aug 04 '13 at 13:23
6

@mohan - the transaction will NOT be rolled back after procedure execution is complete. The transaction will remain open until you either A) commit it, B) roll it back, or C) disconnect, at which time uncommitted transactions will be rolled back. In addition, if you have an open transaction and execute a DDL statement (CREATE TABLE, DROP INDEX, etc) the open transaction will be committed. Share and enjoy. – Bob Jarvis - Слава Україні Aug 04 '13 at 13:47
+1 ^ Missed the 'And all the transactions will be rolled back after the procedure execution is completed' – Vrashabh Irde Aug 04 '13 at 13:50

score 6 · Answer 2 · answered Oct 03 '13 at 17:28

6

In the PL/SQL Developer client, you control the autocommit of SQL Window transactions via Preferences.

enter image description here

answered Oct 03 '13 at 17:28

Michael O'Neill

946
7
22

PL SQL Auto Commit on execution

2 Answers2

Linked