Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Log sqlplus output from Shell

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Log sqlplus output from Shell
# 1  
Old 06-15-2011
Tools Log sqlplus output from Shell
UNIX Gods,

I'll be running this script from CRON. I need to log the status of each of the six sqlplus calls into a file when this job is kicked off. Any suggestions?

Thanks in advance.


Code:
#!/bin/ksh
export USAGE="USAGE: `basename $0` -e <DBUSER> <DBPASSWD> <TNSNAME>"
if [ $# -lt 3 ]; then
  echo ${USAGE}
  exit 1;
fi
SCRIPTHOME=`pwd`
TMS_USER=$1
TMS_PWD=$2
TMS_DATABASE=$3
LL="${TMS_USER}/${TMS_PWD}@${TMS_DATABASE}"
dow()                                                                   
{       
perl -e '
use POSIX qw(strftime);
@time=gmtime(time -(4*3600)); #=> GMT -4
$day = strftime("%A",0,0,0,$time[3],$time[4],$time[5],-1,-1,-1);
print "$day\n"'                                                            
}         
echo "$(dow `date "+%Y-%m-%d"` )"
#day = "$(dow `date "+%Y-%m-%d"` )"
 
if [[ "$(dow `date "+%Y-%m-%d"` )" = "Monday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2005'" "'01-JAN'" "'31-MAR'"
elif [[ "$(dow `date "+%Y-%m-%d"` )" = "Tuesday" ]]; then
 cd ${SCRIPTHOME}
 sqlplus -s ${LL} @./MTL_CST_TXN_COST_DETAILS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_MATERIAL_TRANSACTIONS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_TRANSACTIONS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_TXN_REQUEST_HDR_LINE_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./RCV_SHIPMENT_HDR_LINE_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 sqlplus -s ${LL} @./MTL_CYCLE_COUNT_ITEMS_PRE_OUTAGE.sql "'2006'" "'01-JAN'" "'31-MAR'" 
 
fi 
 
echo ""
echo ""
echo ""
echo "Deployments Complete"

# 2  
Old 06-15-2011
How about "spool" with "append" option in all these .sql files.
This User Gave Thanks to panyam For This Post:
WhoDatWhoDer
# 3  
Old 06-15-2011
Something like :
Code:
runsql () {
sqlplus /nolog << EOF
conn user/pass@$ORACLE_SID
@$1
exit
EOF
}
for i in *.sql # (array, list, whatever)
do
runsql $i | grep ORA-
if [[ $? -gt 0 ]]; then
printf "$i has ORA errors \n" >> rpt.txt
fi
done

This is only a simple example, you can extend it as you wish.

If you require exit codes from sqlplus ($?), you need to code it inside every sql you run.
This User Gave Thanks to Peasant For This Post:
WhoDatWhoDer
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Passing sqlplus output to shell variable

Hi , I am using below code : for i in `ps -ef|grep pmon|awk {' print $8 '}|cut -f3 -d'_'|grep -v '^grep'` do ORACLE_SID=$i export ORACLE_SID; dest=`sqlplus "/ as sysdba" <<EOF set heading off feedback on verify off select DESTINATION from v\\$archive_dest where target in... (5 Replies)
Discussion started by: admin_db
5 Replies

2. Shell Programming and Scripting

Sqlplus function output to bash

Hi, I would like to have the output from an Oracle procedure be captured into a bash variable, then emailed to me when it runs on the cron daily as such: ~~~~~bash script~~~~~~~~~~~ #!/bin/bash shellvar=`sqlplus -s <<EOF execute test(); commit; exit; EOF` echo $shellvar mail -s "email... (1 Reply)
Discussion started by: inlinesidekick
1 Replies

3. Shell Programming and Scripting

Sending sqlplus output to a shell variable

I am trying to import a sqlplus output into a shell variable but it doesnt seem to be working. set -x export DEPENDENT_CR_NO=`sqlplus -s /nolog <<EOF conn username/passwd set heading off select dependency from custom_patches where patch_name='PATCH.zip'; exit; EOF` echo $DEPENDENT_CR_NO ... (2 Replies)
Discussion started by: beginer314
2 Replies

4. Solaris

sqlplus output from ksh.

Hi All, I have the below simple script. It runs just fine by itself when I manually invoke it. But once I put it in the crontab with entry: * * * * * /users/myuser/test.ksh >> /users/myuser/log/test.txt" It does NOT print the returned value ($REMAIN) from the DB!? The result in the... (3 Replies)
Discussion started by: steve701
3 Replies

5. Shell Programming and Scripting

Problems with storing oracle sqlplus query output shell script

Hello everyone, I have a RHEL 5 system and have been trying to get a batch of 3-4 scripts each in a separate variables and they are not working as expected. I tried using following syntax which I saw a lot of people on this site use and should really work, though for some reason it doesn't... (3 Replies)
Discussion started by: rockf1bull
3 Replies

6. Shell Programming and Scripting

Format the output from sqlplus while writing to log file.

Hi I have developed bash script to connect to database and execute .sql files. I am logging some statements in to log file using echo. While logging I am adding the date in front of the log statements which makes sense. I am unable to add date in front of output from the sqlplus and sqlldr,... (8 Replies)
Discussion started by: murtymvvs
8 Replies

7. Shell Programming and Scripting

output arguments from a sqlplus

Hi. I need to output a 4 queries result into another application using result=`sqlplus -s ${3}/${4}@${2} << EOF ... query1 query2 query3 query4 .... echo "$metrics1" and returning those individual values into another app. (query1 and 3compute one value, query 2 and 4 compute 4... (3 Replies)
Discussion started by: shell_zen
3 Replies

8. Shell Programming and Scripting

sqlplus error output to different error log file

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... (2 Replies)
Discussion started by: tomasba
2 Replies

9. UNIX for Dummies Questions & Answers

Getting output parameter in sqlplus

I need to get the output parameter from a stored procedure in sql plus using shell script. Can anyone help me please ... (1 Reply)
Discussion started by: risshanth
1 Replies

10. UNIX for Advanced & Expert Users

sqlPlus output with new line character

Hi, I have a script which calls a sqlplus command and i'm saving the output in a variable. Z=`sqlplus -s $TC_ORACLE_USER/$TC_ORACLE_PASSWORD@$TC_CONNECT_STRING <<eof set echo off set head off set serveroutput on; set feedback off; select description from period where trunc(sysdate)... (2 Replies)
Discussion started by: decci_7
2 Replies
Login or Register to Ask a Question