Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Log sqlplus output from Shell
Top Forums Shell Programming and Scripting Log sqlplus output from Shell Post 302530914 by WhoDatWhoDer on Wednesday 15th of June 2011 10:45:40 AM
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"

 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT DEBIAN

sql::translator::schema::procedure

SQL::Translator::Schema::Procedure(3pm) 		User Contributed Perl Documentation		   SQL::Translator::Schema::Procedure(3pm)

NAME

       SQL::Translator::Schema::Procedure - SQL::Translator procedure object

SYNOPSIS

	 use SQL::Translator::Schema::Procedure;
	 my $procedure	= SQL::Translator::Schema::Procedure->new(
	     name	=> 'foo',
	     sql	=> 'CREATE PROC foo AS SELECT * FROM bar',
	     parameters => 'foo,bar',
	     owner	=> 'nomar',
	     comments	=> 'blah blah blah',
	     schema	=> $schema,
	 );

DESCRIPTION

       "SQL::Translator::Schema::Procedure" is a class for dealing with stored procedures (and possibly other pieces of nameable SQL code?).

METHODS

   new
       Object constructor.

	 my $schema = SQL::Translator::Schema::Procedure->new;

   parameters
       Gets and set the parameters of the stored procedure.

	 $procedure->parameters('id');
	 $procedure->parameters('id', 'name');
	 $procedure->parameters( 'id, name' );
	 $procedure->parameters( [ 'id', 'name' ] );
	 $procedure->parameters( qw[ id name ] );

	 my @parameters = $procedure->parameters;

   name
       Get or set the procedure's name.

	 $procedure->name('foo');
	 my $name = $procedure->name;

   sql
       Get or set the procedure's SQL.

	 $procedure->sql('select * from foo');
	 my $sql = $procedure->sql;

   order
       Get or set the order of the procedure.

	 $procedure->order( 3 );
	 my $order = $procedure->order;

   owner
       Get or set the owner of the procedure.

	 $procedure->owner('nomar');
	 my $sql = $procedure->owner;

   comments
       Get or set the comments on a procedure.

	 $procedure->comments('foo');
	 $procedure->comments('bar');
	 print join( ', ', $procedure->comments ); # prints "foo, bar"

   schema
       Get or set the procedures's schema object.

	 $procedure->schema( $schema );
	 my $schema = $procedure->schema;

   equals
       Determines if this procedure is the same as another

	 my $isIdentical = $procedure1->equals( $procedure2 );

AUTHORS

       Ken Youens-Clark <kclark@cshl.org>, Paul Harrington <Paul-Harrington@deshaw.com>.

perl v5.14.2							    2012-01-18				   SQL::Translator::Schema::Procedure(3pm)
All times are GMT -4. The time now is 04:49 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy