Commit and rollback in Oracle stored procedure

Question

In my Oracle stored procedure, I have multiple insert and update statements like below:

create or replace PROCEDURE SPTest
AS 
BEGIN 
 insert into emptest(empid,empname,deptno)
 (1,'ravi',10);
 insert into test1(id,name,sal)
 (1,'raju',4444);
 update emptest set empname='hari' where empid=1;
END;

If I get any error in any statement, I want to rollback all insert and update statements.

How can I implement commit and rollback in this stored procedure?

Answer 1

You can do this by using a save point.

CREATE OR REPLACE PROCEDURE SPTest
AS 
BEGIN
 -- We create a savepoint here.
 SAVEPOINT sp_sptest;
 
 INSERT INTO emptest(empid, empname, deptno)
 VALUES(1, 'ravi', 10);
 
 INSERT INTO test1(id, name, sal)
 VALUES(1, 'raju', 4444);
 
 UPDATE emptest
 SET empname = 'hari'
 WHERE empid = 1;
 
-- If any exception occurs
EXCEPTION
 WHEN OTHERS THEN
 -- We roll back to the savepoint.
 ROLLBACK TO sp_sptest;
 
 -- And of course we raise again,
 -- since we don't want to hide the error.
 -- Not raising here is an error!
 RAISE;
END;

The commit will typically be left to the caller. This construct just guarantees that either all of the inserts and the update are done, or none of them is.

On the most outer level, Oracle will do a rollback by itself. In other words, if you do EXEC sptest(); on the SQL+ command line, it will be expanded automatically to

DECLARE
BEGIN
 sptest();
EXCEPTION
 ROLLBACK;
 RAISE;
END;

But if a calling procedure has exception handling, this doesn't happen, since the exception may not get to that outer level. So you may end up with the inserts done, the update throwing an exception which may be caught and handled by the caller, so that the update will have failed but the inserts succeeded.

By doing a rollback in the stored procedure to the beginning of the procedure, you ensure that all of them succeed, or none of them do.

Answer 2

--Rollback on Exception else Commit;
--SET TRANSACTION READ WRITE;
 BEGIN
 SAVEPOINT ABC; 
 update table_A set column1 ='Y' where id = 69;
 update table_A set column2 ='S' where id = 70; 
 EXCEPTION
 WHEN OTHERS THEN 
 ROLLBACK TO ABC; 
COMMIT;
END;
--createdon is of datetime type and has an exception. So rollback will happen.

• peterh: Welcome on the DBA SE! Note, your answer looks much better if you give an answer, not only a code snippet.