Ignore ORA-28011 in sqlplus in bash script

Question

I am using sqlplus in a shell script and I am using WHENEVER SQLERROR EXIT 8 and WHENEVER OSERROR EXIT 9 so that I can catch errors using $?.

I will be putting this code on a server that I know gets the password expiry warning/error 'ORA-28011'.

My question is, will my script catch on 'ORA-28011' even though it isn't really an error? If so, how would I go about ignoring it?

My (simplified) code, if it helps:

[...]    
CONNECTION_STRING=$USER/$PASS@$TNS

RESULT=$(sqlplus -s /nolog <<-EOF
    WHENEVER OSERROR EXIT 9;
    WHENEVER SQLERROR EXIT 8;
    $OPTIONS

    CONNECT $CONNECTION_STRING  

    $DB_SQL

    COMMIT;
EOF)

RETURN_CODE=$?

echo "db_exec: Result -> $RETURN_CODE\n$RESULT"

if [ $RETURN_CODE -eq 0 ]
then
    echo "$RESULT"
    return 0
else
    echo "db_exec: Failed"
    return 1
fi

put your user/pass in a secure file and use @filename to connect , instead of exit you can put a case statement and ignore ora-28011 — michael501
– michael501, Commented Mar 30, 2015 at 22:04

Mat · Accepted Answer · 2015-04-17 12:11:38Z

In case anyone is interested, I solved this problem.

ORA-28011 and ORA-28002 do not cause SQL*Plus to exit when using WHENEVER SQLERROR EXIT # or WHENEVER OSERROR EXIT # but will appear in the result. Therefore the code in my question will work, but I need to remove these errors. My updated code is below:

# Run the SQL with the options specified
RESULT=$(sqlplus -s /nolog <<-EOF
    WHENEVER SQLERROR EXIT 4;
    WHENEVER OSERROR EXIT 5;
    SPOOL $TEMP_FILE;
    $DB_OPTIONS
    $DB_CONNECT
    $DB_SQL
    COMMIT;
EOF)

# Save the return code
RETURN_CODE=$?

# Log the result
echo "Result -> Code: $RETURN_CODE\n$RESULT" 1>&2

if [ $( grep -cE '^ORA-28002:|^ORA-28011:' $TEMP_FILE) -ge 1 ]
then
    echo "Warning -> Password Expiry \n$(grep '^ORA-' $TEMP_FILE)" 1>&2
fi

# Check the return code and catch any SQL*Plus (SP2-) errors that might not have presented an error code
if [ $RETURN_CODE -eq 0 ] && [ $(grep -c '^SP2-[0-9][0-9][0-9][0-9]' $TEMP_FILE) -eq 0 ]
then
    # Echo the result, but remove any lines regarding password expiry
    echo "$RESULT" | grep -v "^ERROR:" | grep -v "^ORA-[0-9][0-9][0-9][0-9][0-9]"
    rm $TEMP_FILE
    echo "Success" 1>&2
    return 0
elif [ $RETURN_CODE -eq 4 ]
then
    echo "Failed -> SQL Error \n$(grep '^ORA-' $TEMP_FILE) $(grep '^SP2-' $TEMP_FILE)" 1>&2
    return 4
elif [ $RETURN_CODE -eq 5 ]
then
    echo "Failed -> OS Error \n$(grep '^ORA-' $TEMP_FILE) $(grep '^SP2-' $TEMP_FILE)" 1>&2
    return 5
elif [ $(grep -c "^SP2-[0-9][0-9][0-9][0-9]" $TEMP_FILE) -ne 0 ]
then
    echo "Failed -> SQL*Plus Error \n$(grep '^SP2-' $TEMP_FILE)" 1>&2
    return 6
else
    echo "Unknown error -> $RETURN_CODE\n$RESULT" 1>&2
    return 3
fi

Collectives™ on Stack Overflow

Ignore ORA-28011 in sqlplus in bash script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related