CODEWITHSUNDEEP
oracle-databaseplsql
user149691
user149691

Reputation: 607

Oracle. How do I implement nested transactions

I have several sql files that are executed one after another in a script like this: 

@script-A.sql;
@script-B.sql;
@script-C.sql;
...

every script file does something usefull and looks like this:

DECLARE 
...  
BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE SOME-TABLE';  
  -- lots of inserts into SOME_TABLE
  COMMIT;
END;
/

I have to wrap sql files execution in one big transaction. If execution of any sql file is failed than all previous sql file changes should be rollbacked.

How can I do this?

p.s

as @Mat noticed DDL changes (truncate table) can't be rollbacked. I'm free to refactor this line of code if it's required.

Upvotes: 1

Views: 2362

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Here the best way which you can use to control the transaction is by keeping the exception handling inside every script so that if any transaction fails the whole changes will be reverted. The only condition here is to keep the COMMIT at the very end so that on successful transaction of the last wil fire COMMIT. Hope below snippet helps.

------------------------------Script 1-----------------------------------------


--Simple query to delete from records
DECLARE
  p_err_cd PLS_INTEGER;
  p_err_msg VARCHAR2(100 CHAR);
BEGIN
--  EXECUTE IMMEDIATE 'DELETE EMP ';
  INSERT INTO EMP
  SELECT '123', 'AVRJIT', 'DB', 12, SYSDATE, 1200, 123, 10, '123' FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
  p_err_cd := SQLCODE;
  p_err_msg:=SQLERRM;
dbms_output.put_line('Transaction Rollback');
END;
/

---------------------------------Script 2 ----------------------------------
--Simple query to delete from records
DECLARE
  p_err_cd PLS_INTEGER;
  p_err_msg VARCHAR2(100 CHAR);
BEGIN
--  EXECUTE IMMEDIATE 'DELETE EMP ';
  INSERT INTO EMP
  SELECT '123A', 'AVRJIT', 'DB', 12, SYSDATE, 1200, 123, 10, '123' FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
  p_err_cd := SQLCODE;
  p_err_msg:=SQLERRM;
dbms_output.put_line('Transaction Rollback');
END;
/
-----------------------------------------------------------------------------
Since EMP table has EMPNO as NUMBER field so the second script will fail.
While running the main executing file i.e Full_script.sql


set serveroutput on;
@C:\Av_test_script\Stack_overflow\full_script.sql


PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed. --> Failed as EMPNO column here is of Invalid datatype

Transaction Rollback

Upvotes: 1

Ilia Maskov
Ilia Maskov

Reputation: 1898

At first you should eliminate DDL and commit from your code. Then add one commit line after all script execution. And then finally all you need is to set SQL*Plus parameter to rollback your transaction on firts error. So you need to set WHENEVER SQLERROR EXIT ROLLBACK http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm

You can see some example of usage here Oracle 11 - sqlplus - rollback the whole script on error - how?

Upvotes: 0

Related Questions