question

sqlLearner 1 avatar image
sqlLearner 1 asked

How to perform a ROLLBACK

I wrote the stored procedure that deletes a row from the employee table when enter the primary key. What i want to do is add a ROLLBACK feature, if I was to delete the wrong row to revert back to original table. How would I go about modifying this SP? I am using Oracle SQL Developer CREATE OR REPLACE PROCEDURE DeleteRowEmp (p_EmpSSN IN employee.ssn%type) AS BEGIN Delete employee where ssn = p_empssn; COMMIT ; END DeleteRowEmp; / show errors exec DeleteRowEmp ('666884444');
stored-proceduresoracleoracle-sql-developerrollback
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sp_lock avatar image
sp_lock answered
How do you know if you have deleted the incorrect row? On error? On duplications? On FK violation? Normally I dont answer Oracle questions, but my hunger for knowledge has got the better of me. Reading through the docs, similar to MSSQL you can create specific transactions and rollback each of the transaction back in turn. Also, in oracle you can create specific SAVEPOINT than will allow you to [rollback][1] to a point in your PL/SQL . It does look like the best way forward is to create an [EXCEPTION][2] so you can capture the exact type of error (using the [WHEN][3] function) BEGIN INSERT INTO taba VALUES ('Hello', 'World'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE ('It matters'); END; [1]: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#i7105 [2]: http://psoug.org/definition/EXCEPTION.htm [3]: http://psoug.org/definition/WHEN.htm
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.