Error Handling while running sqlplus from shell scripts

Question

I need to validate whether DB connection is success/failure.

This is my code

report=`sqlplus -S /nolog << EOF
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
connect <<username>>/<<Password>>@hostname:port
set linesize 1500
set trimspool on
set verify off
set termout off
set echo off
set feedback off
set heading on
set pagesize 0

spool extract.csv
<<My SQL Query>>
spool off;
exit;
EOF`

I have tried the below option based on the thread Managing error handling while running sqlplus from shell scripts but its picking the first cell value rather than the connection status.

if [ $report != 0 ]
then
echo "Connection Issue"
echo "Error code $sql_return_code"
exit 0;`enter code here`
fi

Please advise.

score 1 · Answer 1 · answered May 12 '16 at 21:21

I needed something similar but executed it a bit differently.

First, I have list.txt which contains the databases that I would like to test. I am using wallet connections but this could be edited to hold username/password.

list.txt:

DB01 INSTANCE1.SCHEMA1 
DB02 INSTANCE2.SCHEMA2  
DB03 INSTANCE3.SCHEMA3  
DB04 INSTANCE4.SCHEMA4

I have OK.sql which contains the query that I want to run on each database.

OK.sql:

select 'OK' from dual;
exit

Last, I user test.sh to read list.txt, attempt to connect and run OK.sql on each line, and record the result in (drumroll) result.txt.

test.sh:

. /etc/profile
rm result.txt
while read -r name wallet; do
    echo "BEGIN-"$name
    if (sqlplus -S /@$wallet @OK.sql < /dev/null | grep -e 'OK'); then
        echo $name "GOOD" >> result.txt
    else
        echo $name "BAD" >> result.txt
    fi
    echo "END-"$name
done < list.txt

After the run check your result.txt.

result.txt:

DB01 BAD
DB02 GOOD
DB03 GOOD
DB04 GOOD

I hope this helps.

Error Handling while running sqlplus from shell scripts

1 Answers1