sqlplus error output to different error log file

Shell Programming and Scripting
1

HELLO,

I am using such a command to write oracle sqlplus query result to text file:
sqlplus -S xxx/xxx@xxxxxxx @\tmp\2.sql>\tmp\123.txt
Is it possible to script that:
If command succesfull write in \tmp\log.txt:
timestamp and "succeded"
and create 123.txt with results
else
If error occured (ORA error, or OS error), write in \tmp\log.txt file
timestamp "failed" error code
(but don't write error in file 123.txt - dont create file 123.txt)

Many thanks,

Tomas

2

Tomas,

Seems like you are asking for too much to write in a single command! I do not think it is possible to execute However, we can certainly write a shell script that satisfies your requirement, albeit with some modifications.

 
timestamp=`date '+%d%b%Y%H%M%S'`
sqlplus -S xxx/xxx@xxxxxxx @\tmp\2.sql > /tmp/result.txt
sqlplus_stat="$?"

if [[ ${sqlplus_stat} -eq 0 ]]; then
  echo "${timestamp} Successful" > /tmp/log.txt
  cp -p /tmp/result.txt /tmp/123.txt
else
  echo "${timestamp} Failure" > /tmp/log.txt
  cat /tmp/result.txt >> /tnp/log.txt
fi

HTH,:cool:

Regards,

Praveen

3

I only think that will work if the input sql file exists. If it doesn't exist, the exist code sqlplus returns to the shell is 0.