Problem with inserting data

Shell Programming and Scripting
1

Hi,

i am doing a simple script to insert logs into particular table through shell script

when i run the script, it is inserting null value.

Any explanation on this. Here is my script.

#!/bin/sh
export db_connection_url=apps/apps@VIS

LOG="/db01/apps/applvis/VIS/apps/apps_st/appl/xxhex/12.0.0/sql/test1.log_rundate_`date +%Y%m%d`.`date +%H%M%S`"

RETVAL=`sqlplus -s $db_connection_url <<EOF>>$LOG

whenever sqlerror exit sql.sqlcode;

set serveroutput on
@test1.sql

exit;
EOF`

ERROR_CODE=$?

if [ $ERROR_CODE != 0 ]

then

  echo "There are some errors. ErrorCode: $ERROR_CODE" >>$LOG;
  var= egrep -rw 'ORA' $LOG
  echo $var

ProcessFailure=`sqlplus -s $db_connection_url <<EOF>>$LOG

whenever sqlerror exit sql.sqlcode;
set serveroutput on

Insert into error_log(v_errormsg) values ('$var');

exit;
EOF`  

else

echo "Command executes successfully. ErrorCode: $ERROR_CODE">>$LOG;
 
ProcessFailure=`sqlplus -s $db_connection_url <<EOF>>$LOG

whenever sqlerror exit sql.sqlcode;
set serveroutput on

Insert into error_log(v_errormsg) values ($'$var');

exit;
EOF`
fi

OS: UNIX
Database: Oracle 11g

2

Corrections:

Put command inside sub-shell

var=$( egrep -rw 'ORA' $LOG )

Also remove extra dollar sign in second insert statement:

Insert into error_log(v_errormsg) values ($'$var');

The best way of understanding problem with your script is debugging it yourself by setting xtrace & verbose.

#!/bin/sh -xv
3

Thank you sir, really helpful.. terrific answer