CODEWITHSUNDEEP
linuxoracle-databaseunix
redsoxlost
redsoxlost

Reputation: 1235

Logging sql error from inside shell script

i am trying to implement a error logging mechanism in shell script which will also give me errors inside a sql statement

I am planning to call my script from inside another script and redirecting the error to a logfile.. is there any better option ? please help.

#!/bin/sh

./test.sh 2>&1 >> log_1.log

test.sh contains the follwing code 

## testing error logging in sql

result=`sqlplus -s $username/$passwd@$db <<EOF
set serveroutout on
set pagesize 0
set heading off
set echo off
set feedback off
select first_name from employees;
exit;
EOF
if [ $? -ne 0 ]; then
   echo "Error exists"
else
echo "$result"
fi

--- Edited after testing the code given by Alex Pool

I did the changes but whenever I am getting an SQL error,log file is not getting generated instead the error is being shown at the command line..

Script name -test_error_log.sh 

#!/bin/sh

output=`sqlplus -s -l hr/hr@xe << EOF
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback
set serveroutput on
set heading off
set pagesize 0
set echo off

select **e.firs_name   --- wrong field name**
       ,d.department_name from 
employees e , departments d
where e.department_id=d.department_id ;

exit;
EOF`

echo $output

I am calling it from caller_shell.sh in the following way

script name : caller_shell.sh

#!/bin/sh

./test_error_log.sh 2>&1 log_file

When I execute ./caller_shell.sh from command line I am getting the error a but not at the log_file but at the screen

ERROR at line 1: ORA-00904: "E"."FIRS_NAME": invalid identifier

Please let me know how to resolve this ..

Upvotes: 2

Views: 6789

Answers (1)

Alex Poole
Alex Poole

Reputation: 191425

You can use whenever sqlerror to make SQL*Plus exit with an error code, which the shell script will see as the return code:

result=`sqlplus -l -s $username/$passwd@$db <<EOF
whenever sqlerror exit failure rollback
whenever oserror exit failure rollback
set serveroutput on
set pagesize 0
set heading off
set echo off
set feedback off
select first_name from employees;
exit 0;
EOF`

It will exit when the error is seen, and nothing after the point of failure will be attempted. Using failure makes it a generic failure code, but you can specify a particularly value if you prefer. But be aware of shell limits if you use your own value; in most shells anything above 255 will loop back round to zero, so it's not a good idea to try to exit with the SQL error code for example as you might get a real error that happens to end up as zero after it's been mod'ed. Using rollback means that if a failure occurs partway through a script it will roll back any (uncommitted) changes already made.

This will catch SQL errors and PL/SQL errors (unless those are caught by an exception handler and not re-raised), but won't catch SQL*Plus-specific errors - i.e. those starting SP-, such as from an invalid set command.

I've added a -l flag to the sqlplus command so it only tries to connect once, which is helpful for a non-interactive script - sometimes they can hang waiting for subsequent credentials, depending on the context and what else is in the script. I've also fixed the spelling of serveroutput and added a missing backtick...

Upvotes: 3

Related Questions