CODEWITHSUNDEEP
oracle-databaseoracle11g
John Doe
John Doe

Reputation: 3243

How to create and use an Oracle transaction

I want to write a stored proc that delete data from two tables. Should either of the deletes fail I want to make sure that no data was deleted.

This should be a simple task, but I never worked in Oracle before. I'm not if I should be using TRY/CATCH, TRANSACTIONS or SAVEPOINTS.

Any guidance would be appreciated.

Currently I have:

CREATE OR REPLACE PROCEDURE SP_DELETE_STUFF 
(
  GROUPNAME IN VARCHAR2  
) AS 
BEGIN

  SAVEPOINT Original_Start;

  -- First delete all permissions for a given group
  DELETE FROM my_table_1
  WHERE group_name = GROUPNAME;

  -- Second delete the group
  DELETE FROM my_table_2
  WHERE group_name = GROUPNAME;

  EXCEPTION 
    WHEN OTHERS THEN
    BEGIN
      ROLLBACK TO SAVEPOINT Original_Start;
      COMMIT;  
    END;
  END

Upvotes: 0

Views: 199

Answers (1)

Justin Cave
Justin Cave

Reputation: 231761

If your goal is just to rollback the changes that a particular call of the stored procedure has made if there is an error, you'd use a savepoint and a rollback to savepoint like you are doing here.

I would question your use of a commit after your rollback to savepoint. That will commit the transaction that the caller of your stored procedure had started. It seems unlikely that you want to commit the caller's changes when your procedure encounters an error. So I would expect that you want to remove the commit.

Not related to transaction scoping, I would also expect that you would want to at least re-raise the exception that you caught so that the caller is aware that there was a failure. I would expect that you would want something like

 EXCEPTION 
    WHEN OTHERS THEN
    BEGIN
      ROLLBACK TO SAVEPOINT Original_Start;
      RAISE;  
    END;

Upvotes: 2

Related Questions