PROBLEM WITH ORACLE QUERY IN UNIX SCRIPT

hi Guys,

i have a problem with oracle query in my unix script..

I'm getting the following error while executing..

./logtab.sh: sqlplus -s "pmutv/pmutv1" << EOFSQL^Jset head off^Jinsert into sess_log(SESSION_NAME,WORKFLOW_NAME,START_TIMESTAMP,SESSION_STATUS,OUTPUT,REJECT)values(smLoadTesUhatgdb1FelkodMan,bLoadTesDomainMan,20110112,Success,36,36);^J^Jexit;^JEOFSQL: not found

enitire code is below

 
#!/bin/sh
###########################################
#                                         #
#  THIS SCRIPT FETCHES THE INFORMATION    #
#  FROM THE INFORMATICA LOG FILE AND      #
#  LOADS THE DATA IN TO THE SESS_LOG_TAB  #
#                                         #
###########################################
find /export/home/pmutv/data/  -type f -name *.4 -print > /export/home/pmutv/auto/LogFiles.txt
for i in `cat /export/home/pmutv/auto/LogFiles.txt | cut -f6 -d"/"`
  do
SESSION_NAME=`cat /export/home/pmutv/data/$i | head -1 | awk '{print $3}' | cut -f2 -d"[" |cut -f1 -d"]"`
WORKFLOW_NAME=`grep -w Workflow /export/home/pmutv/data/$i | awk '{print $4}' | cut -f2 -d"[" | cut -f1 -d"]"`
START_TIMESTAMP=`grep -iw "load start time" /export/home/pmutv/data/$i | cut -c 18-41 |  cut -f2,3,5 -d" " | sed 's/ //g'`
SESSION_STATUS=`cat /export/home//pmutv/data/$i | tail -1 | awk '{print $5}'`
OUTPUT=`grep -iw "output rows" /export/home/pmutv/data/$i | head -1 | cut -f1 -d"," | cut -f4 -d" " | cut -f2 -d"[" | cut -f1 -d"]"`
REJECTED=`grep -iw "output rows" /export/home/pmutv/data/$i | head -1 | cut -f1 -d"," | cut -f4 -d" " | cut -f2 -d"[" | cut -f1 -d"]"`
start_date=$START_TIMESTAMP
 New_date=`nawk -v Date=${start_date} '
 BEGIN { Months="  JanFebMarAprMayJunJulAugSepOctNovDec"
 year = substr(Date, 6, 4) + 0;
 day = substr(Date, 4, 2) + 0;
 month = index(Months, substr(Date, 1, 3))/3;
 printf "%04d%02d%02d", year, month, day;
 exit;
}
'`
if [ $SESSION_STATUS != "completed" ]; then
   SESSION_STATUS="Failed"
   else
   SESSION_STATUS="Success"
  fi
echo "Session Name------> $SESSION_NAME"
echo "Workflow name-----> $WORKFLOW_NAME"
echo "Load Start Time---> $New_date "
echo "Session Status----> $SESSION_STATUS"
echo "Inserts ----------> $OUTPUT"
echo "Rejects-----------> $REJECTED"
'sqlplus -s "pmutv/pmutv1" << EOFSQL
set head off
insert into sess_log values('$SESSION_NAME','$WORKFLOW_NAME','$New_date','$SESSION_STATUS','$OUTPUT','$REJECTED');
exit;
EOFSQL'
echo "$?"
done

THANKS IN ADVANCE :slight_smile:

 
'sqlplus -s "pmutv/pmutv1" << EOFSQL
set head off
insert into sess_log values('$SESSION_NAME','$WORKFLOW_NAME','$New_date','$SESSION_STATUS','$OUTPUT','$REJECTED');
exit;
EOFSQL'

Remove the single quote " ' " and just execute it.i.e

sqlplus -s "pmutv/pmutv1" << EOFSQL
set head off
insert into sess_log values('$SESSION_NAME','$WORKFLOW_NAME','$New_date','$SESSION_STATUS','$OUTPUT','$REJECTED');
exit;
EOFSQL
1 Like

Hi,

Thanks for the replty thats working fine now..

:slight_smile: