0

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.

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.