SQLPlus for Oracle 11 not rolling back

Question

I have a shell script which invokes an SQL file. However even with AUTOCOMMIT OFF and on SQLERROR EXIT ROLLBACK. Sqlplus fails to rollback.

My sql file has 3 lines 3 are correct and 1 is incorrect. For example:

INSERT INTO TEST_ROUTING VALUES (24, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (25, 'ROUTING');
INSERT INTO TEST_ROUTING VALUES (26, 'ROUTING);

My shell script invokes this SQL as follows:

$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
        connect $DB_USER/$Password1@$Database1
        AUTOCOMMIT OFF
        @$File1
        WHENEVER SQLERROR EXIT ROLLBACK;
EOF

if [ $? != 0 ]; then
    echo "The SQL failed. Please refer to the log for more information "
    echo "Error code $?"
    echo "8. Outside While "
    while read LINE; do
        -------
    done
fi

But this never rolls back the 3 statements. It commits 2 and give an error on the last lines.

score 1 · Answer 1 · answered Sep 06 '13 at 13:06

the WHENEVER SQLERROR part should come before actually executing the INSERT statements (+ a SET is missing before AUTOCOMMIT):

$SQLPLUS_PATH/sqlplus -s /nolog <<-EOF>> ${LOGFILE}
    connect $DB_USER/$Password1@$Database1
    SET AUTOCOMMIT OFF
    WHENEVER SQLERROR EXIT ROLLBACK;
    @$File1
EOF

SQLPlus for Oracle 11 not rolling back

1 Answers1