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?

Answer 1

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