Query a oracle DB when fail put in in the error log

Shell Programming and Scripting
1

Hi all,

I'm trying to create a script that does the following:
Connect with SQLplus to my oracle db. Do a select 1 from dual; and when I don't get a value back. Than put the $DATE in a logfile.

What I have until now:

#!/bin/bash

# values
GEN_ERR=1 #error 1 code
USER=username
PASS=mypass
SIDORA=oracle.db
LOG=/var/log/failure.log

#Login to the DB
sqlplus -s /nolog <<EOF 
connect $USER/$PASS@$SIDORA

select 1 from dual;

EOF

errorCode=$?    # checks if the last operation (sqlplus) was completed successfully or not
if [ ${errorCode} -ne 0 ]
then
echo "DATE" >> $LOG
exit ${CRED_ERR}
fi

Any help would be nice !

2

Hi,

I would've thought that sqlplus's return value will be set according to system events, not internal SQL parsing?

Is it entirely non-functional presently?

3

Well the query work.
I get a 1/1 respone, but when I close the DB de response is something like:
Unable to connect, no response from host.

At the first respone, de script don't have to do anything!
But at the second response the script has to put the date and time in logfile.

4

I believe the status code $? gives the status of the command sqlplus in your above script and not for the 'select ...' statement inside it . IMO re-direct the output to a file and check for its size.Something like..

OUTFILE=outfile.txt # Declare a output file
#Login to the DB
sqlplus -s /nolog <<EOF > $OUTFILE
connect $USER/$PASS@$SIDORA
select 1 from dual;
exit;
EOF

if [[ ! -s ${OUTFILE} ]]
then
        echo "$DATE" >> $LOG
        exit ${CRED_ERR}
fi

Or you could grep for any 'ORA-..' error in the out file to check if the connection is success or not.

5

That could work.

But the output of the statement is
1
----------------------
1

so when I do a grep on the 1 it will also write to the logfile when the date in that file is the date and time are 10:11:00

6

Well then don't grep for "1" i.e. do not grep for success; grep for failure.

If there is a "^ORA-" pattern in the outfile, then you are certain that something went wrong.

If not, then everything should've worked fine (note: should've, not must've).

If you do want to test for success as well as failure explicitly, then you may want to provide a column alias like so -

select 1 my_test_value from dual;

or maybe just fetch a literal string from Oracle -

select 'my_test_string' x from dual;

and then grep for "my_test_string" otherwise grep for "^ORA-".

tyler_durden

7

Or else you should go for a more refined grep'ing/sed than just a simple grep like..

grep '1' inputfile
8

Ok I've finished the script.
Any remarks? Please let me know.

#!/bin/bash

# values
USER=username
PASS=password
SIDORA=sid
LOG=/var/log/$SIDORA.log
DATUM=date
#Login to the DB
sqlplus -s /nolog <<EOF > tmpfile.log
connect $USER/$PASS@$SIDORA

select 1 from dual;

EOF

errorCode=$(head -2 tmpfile.log | awk '{ print $1 }' | grep 1)
# checks if the last operation (sqlplus) was completed successfully or not
if [ $errorCode != 1 ];

then
$DATUM >> $LOG
fi
9

The only potential problem I see is if you get an error and the "ORA-" message contains a 1 (for example: ORA-01017 Invalid username or password), you would get a false positive.

You could just return your results to a variable, test the exit code and the results and react accordingly:

I don't have my server up to test, but this should work for you.

I use ksh so please excuse any syntax differences...

#
# Using <<-EOF allows you to TAB (can not be spaces)
# the EOF over for code alignment, if necessary.
#
DB_STATUS=$(sqlplus -s /nolog <<-EOF
   connect $USER/$PWD@$DB

   set pages 0 trimout on trimspool on
   set echo off termout off sqlprompt ''

   select 1 from dual;

   exit

EOF)

EXIT_CODE=$?

if [[ "${EXIT_CODE}" -ne 0 ]] && [[ "${DB_STATUS}" -ne 1 ]]; then
   echo "Error mesage" >> ${LOG}
fi