sqlplus error output to different error log file

tomasba · #1 (**permalink**) 02-11-2009

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

sunpraveen · #2 (**permalink**) 02-11-2009

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.

Code:

 
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,

Regards,

Praveen

Prof Mo. · #3 (**permalink**) 03-25-2009

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.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
error running sqlplus from shell file	mmunir	Shell Programming and Scripting	3	02-06-2009 04:52 AM
SQLPLUS error	ssmith001	Shell Programming and Scripting	3	07-22-2008 12:00 PM
Error: Internal system error: Unable to initialize standard output file	firkus	UNIX for Dummies Questions & Answers	2	10-25-2005 03:23 PM
error connecting to sqlplus	papachi	UNIX for Dummies Questions & Answers	2	05-23-2005 11:05 AM
sqlplus -s error capture	w33man	Shell Programming and Scripting	1	03-05-2004 04:26 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread:


	Powered by