PL SQL Auto Commit on execution
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
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