How to rollback automatically if script having error in Oracle sql plus

Question

I need to execute the multiple script having one master sql file. Whenever I used to execute the master calling script named as calling_test.sql if anything error comes need to be rollbacked.

sqlplus USERNAME/PWD@SIR_NAME;
@@calling_test.sql

here is content of calling_test.sql script.

SET echo ON; 
SET define ON; 
SET scan ON; 
define PATH =/krishna/test 
define AB_SCHEMA=AIM 
spool Test_incremental.log 
SET define ON; 
@@&&PATH/AUG/2019-08-28/test1.sql
SET define ON; 
@@&&PATH/AUG/2019-08-29/test2.sql 
SET define ON; 
@@&&PATH /AUG/2019-08-30/test3.sql 
SET define ON;

For future reference please edit your question to provide further details or other improvements. In particular code is hard to read in a comment due to the poor layout and lack of formatting. — APC
– APC, Commented Sep 27, 2019 at 10:26

Littlefoot · Accepted Answer · 2019-09-26 11:39:32Z

1

The scrip should contain something like this:

whenever sqlerror exit rollback

Example:

SQL> create table test (col number);

Table created.

SQL>

SQL script (named p.sql)

whenever sqlerror exit rollback

insert into test values (100);
insert into test values ('A');

Calling it:

M:\>sqlplus scott/tiger@orcl @p.sql

SQL*Plus: Release 11.2.0.1.0 Production on ╚et Ruj 26 13:38:50 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


1 row created.

insert into test values ('A')
                         *
ERROR at line 1:
ORA-01722: invalid number


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

M:\>

Result:

SQL> select * From test;

no rows selected

SQL>

answered Sep 26, 2019 at 11:39

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Krishnakumar Nagarajan Over a year ago

Thanks for the support. but I need to execute multiple scripts in that calling script.sql.

Belayer · Accepted Answer · 2019-09-27 22:13:55Z

You can rollback the actions from called scripts, but in addition to WHENEVER command you must also SET AUTOCOMMIT OFF. Normally when Sqlplus exits a script invoked in a separate file it commits. However the preceding overrides that action. See below: (save each script to the indicated file.)

   ----------------------------------------------------------------------------           -- script mst_0.sql
    create table multi_script_test( id integer, description varchar2(50));
    insert into multi_script_test values( 0, 'Initial before script.');
    commit;

    -- script mst_1.sql
    insert into multi_script_test values( 1, 'Insert from script mst_1');

    -- script mst_2.sql
    insert into multi_script_test values ( 2, 'Insert from script mst_2');

    -- script mst_3.sql
    insert into multi_script_test values ( 3/0, 'oops');

    -- script mst_4.sql
    insert into multi_script_test values ( 4, 'Insert from script mst_4');

    -- main script mst_main.sql
    set echo on
    set autocommit off
    whenever sqlerror continue rollback

    @@c:/so/ora/mst_0.sql
    @@c:/so/ora/mst_1.sql
    @@c:/so/ora/mst_2.sql
    -- following should display rows 0, 1, 2
    select * from multi_script_test;

    -- generate error and due to whenever directive 'rollback' discard rows 1,2
    @c:/so/ora/mst_3.sql

    -- continue script processing, also due to whenever directive 'contunue'
    @c:/so/ora/mst_4.sql
    commit;
    ----------------------------------------------------------------------------

    sqlplus -- complete the signon 

    -- run main script 
    @mst_main


    -- following show show display 0, 4
    select * from multi_script_test;

    exit

Collectives™ on Stack Overflow

How to rollback automatically if script having error in Oracle sql plus

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related