CODEWITHSUNDEEP
oracle-databasestored-proceduresrollback
Hamzeh Hirzallah
Hamzeh Hirzallah

Reputation: 263

Can I rollback after calling oracle procedure?

I have a procedure that calls 2 inner procedures, I want to rollback the procedures call if a condition is true, is this possible?

This pseudo demonstrate what I want to do:

Create or Replace procedure Main

   Call procedure_1();
   Call procedure_2();

   IF X = true THEN
      ROLLBACK;
   END IF;

END Procedure Main;

Upvotes: 2

Views: 2382

Answers (1)

Praveen
Praveen

Reputation: 9335

You can do this Using SAVEPOINT With ROLLBACK

Create or Replace procedure Main

   SAVEPOINT sp_1;
   Call procedure_1();       
   Call procedure_2();

   IF X = true THEN
      ROLLBACK TO sp_1;
   END IF;

END Procedure Main;

Note:

If your stored procedure have any DDL statements like create\alter\drop\truncate etc.. then the savepoint will be invalid as DDL statements issue commit before and after the statement.

You can rollback to a savepoint defined in the current transaction, you cannot rollback to the savepoint after issuing DDL statements/Commit.

Upvotes: 3

Related Questions