Logging sql error from inside shell script

Question

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 ..

Alex Poole · Accepted Answer · 2014-06-09 11:12:21Z

3

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...

edited Jun 9, 2014 at 11:12

answered Jun 9, 2014 at 11:06

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Maheswaran Ravisankar Over a year ago

Great explanation. +1 for -l option.

redsoxlost Over a year ago

@Alex Thank you very much Sir .. Very Informative. whenever sqlerror exit failure rollback

redsoxlost Over a year ago

@Alex What does failure contains? sql err msg ? I dont have machine at this monent so cant really test it.

Alex Poole Over a year ago

@AsfakulIslam - it's just a numeric value; with failure I think it's always 1. But you'll have the real error message in the output. E.g if you don't have an emoloyees table, you'll see the ORA-00942 in the SQL*Plus output, and $? will be 1 - so you'll generate your "Error exists" message too.

redsoxlost Over a year ago

@AlexPoole Also if possible could you please let me know what does exec 2>&1 log_file.txt mean?

|

Collectives™ on Stack Overflow

Logging sql error from inside shell script

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related