1

I am actually calling an SQL script from Unix using the below code. The SQL script has millions of DML's which will be run on my database. The requirement is that if an DML fails then the script should abort with an exit code rather than skipping to the next DML. Currently,it is executing all the DML's without breaking at the DML that failed. We actually need more control on the script. How can we achieve this? Plz help.

SCRIPT BODY

#! /bin/sh

    echo "Script started at `date`"

    #************** Execute the extract sql on Staging database***************#
    `sqlplus -s $CCBSTGID/$CCBSTGPASSWRD@$CCBSTGDBASE <<EOF
    whenever sqlerror exit 2;
    whenever error exit 3;
    set termout on
    set echo on
    set serveroutput on
    set pagesize 0
    set linesize 500
    set heading off
    set verify on
    set feedback on

    spool output1.txt;
    @DMLFile.sql
    spool off;
    quit;

    EOF`

    RC=$?

    echo $RC

    if [ ${RC} != 0 ]
    then
    echo "Script execution failed"
    exit 1
    fi
Improve this question
2
  • 1
    do you really intend to surround the sqlplus <EOF ... EOF block in back-ticks, or are you just unsure about S.O. formatting? As presented, the back-ticks provide no value. Commented Nov 6, 2014 at 17:40
  • Can you please explain a bit about what is inside the @DMLFile.sql ? How are things structured? Commented Nov 6, 2014 at 17:52

2 Answers 2

Reset to default
1

The problem is that you aren't wrapping the DML statements within PL/SQL blocks so the WHENEVER SQLERROR ... isn't working like you expect. Here is a test case demonstrating the issue.

-- File: dmlfile.sql
update a set name = 'A';
foo; -- erroneous command to cause error
update a set name = 'B';

Before...

C:\>perl wrapper.pl

1 row updated.

SP2-0042: unknown command "foo" - rest of line ignored.

1 row updated.

Oops, both updates statements executed; script did not exit after error.

After wrapping with BEGIN and END

-- File: dmlfile.sql
begin
    update a set name = 'A';
    foo; -- erroneous command to cause error
    update a set name = 'B';
end;
/

Running it:

C:\>perl wrapper.pl
        foo; -- erroneous command to cause error
        *
ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00201: identifier 'FOO' must be declared
ORA-06550: line 3, column 2:
PL/SQL: Statement ignored

Also, I don't know of any command WHENEVER ERROR, perhaps you were thinking WHENEVER OSERROR ?

See previous answer: PL/SQL: is there an instruction to completely stop the script execution?

Try wrapping all of your DML in a BEGIN ... END block:

-- DMLFile.sql
BEGIN
    -- your DML statements here
END;
/

or wrap each individual logical block of statements in a block.

-- DMLFile.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;

BEGIN
    -- block 1
END;
/

BEGIN
    -- block 2
END;
/

WHENEVER SQLERROR EXIT is a SQLPlus directive, not standard SQL, but is fairly portable; it works in SQLPlus, SQL Developer, Toad, PL/SQL Developer, SQLsmith and others.

An alternative would be to use some other GUI client around your DML or pass the whole script to a tool that can do batch mode. Toad will stop after one error and prompt you.

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

4 Comments

doesn't the original post include the whenever sqlerror already? And are you saying that is best to have each @dml1.sql file be self contained, with the PL/SQL trap and whenever sqlerror, and then call with sqlplus scott/tiger @dml1.sql from the script? Thanks, interesting post!
@shelter - When I first read his post, I thought he had "millions" of the DML scripts, and they were failing, but it seems perhaps he has one giant one and indeed I missed the fact that he has whenever sqlerror already there. I have to rethink my answer!
Revised my answer. The problem is that his DML must be a bunch of individual SQL statements, not BEGIN/END blocks. If he used BEGIN/END blocks, then the WHENEVER SQLERROR directive would work.
excellent, thanks for the revise. I've dealt with this a little bit at work, but not enough to know by heard and I don't have access today. Good luck to all!
0

SQLPLUS couldn't parse the command so it never made it to SQL. The SP2-0042 error is a sqlplus error, so it doesn't get caught by the WHENEVER SQLERROR EXIT directive. Sadly, There's no equivalent SQLPLUSERROR directive....

However, starting in the 11g sqlplus (not the database), Oracle has provided a error logging facility that can be used to trap various errors.

SQLPLUS> set errorlogging on

I finally found the reference here: SQLPlus User Guide

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.