1

I have written a bash script to connect to sqlplus and execute all the sql scripts in a particular folder. Below is the execute method of the script.

execute() {
if [ ! -d "$DIR_SqlFiles" ]
then
echo "No sql files were found. Cannot find path ${DIR_SqlFiles}"
return 1
fi
echo "`date` :Connecting To ${userName}/******@${serviceName}";
for file in `ls ${DIR_SqlFiles}/*` ; do
echo "`date` :Executing file $file..."
echo "`date` :SQL OUTPUT:";
sqlplus -s ${userName}/${password}@${host}:${port}/${serviceName} <<EOF
WHENEVER OSERROR EXIT 1 ROLLBACK;
WHENEVER SQLERROR EXIT 1 ROLLBACK
@${file};
commit;
quit;
EOF
sql_return_code=$?
if [ ${sql_return_code} != 0 ]
then
echo "`date` ${file} failed"
echo "Error code ${sql_return_code}"
return 1;
fi
done
echo "`date` :completed running all sql scripts in the ${DIR_SqlFiles} folder."
return 0;
}

The problem is when one of the .sql files has ORA-02291: integrity constraint this script will not fail. Its returning 0 and it just prints "0 rows updated" without printing the actual error.

In other cases its working fine. For example, in case of wrong table name (ORA-00942: table or view does not exist) the script will fail and return 1.

Could anyone please point me to the right direction. Why for the first case errors its not failing?

2
  • 1
    Sql*plus always returns exit code 0? Commented Sep 22, 2018 at 19:06
  • 1
    but why in the case of ORA-00942 its returning 1? In my script I explicitly told it to return error "WHENEVER SQLERROR EXIT 1". Its even in the link you provided. Commented Sep 22, 2018 at 19:09

0

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.