Format the output from sqlplus while writing to log file.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Format the output from sqlplus while writing to log file.
# 1  
Old 11-23-2010
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, this is making my log to look odd and weird.
Is there a way to format (add date in front of) these outputs from sqlplus and sqlldr before writing to the log,

Thanks in advance.

Murty.
# 2  
Old 11-23-2010
Can you give me an example of what you want to see.
# 3  
Old 11-23-2010
Currently I am getting something like this

2010-11-23 15:16 CST DEBUG: Start executing test1.sql
PL/SQL procedure successfully completed.
2010-11-23 15:16 CST DEBUG: Completed executing test1.sql
2010-11-23 15:16 CST DEBUG: Start executing SQL loader
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Nov 23 15:16:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 6.
2010-11-23 15:16 CST DEBUG: Completed executing SQL loader


I want something like this...

2010-11-23 15:16 CST DEBUG: Start executing test1.sql
2010-11-23 15:16 CST PL/SQL procedure successfully completed.
2010-11-23 15:16 CST DEBUG: Completed executing test1.sql
2010-11-23 15:16 CST DEBUG: Start executing SQL loader
2010-11-23 15:16 CST SQL*Loader: Release 10.2.0.1.0 - Production on Tue Nov 23 15:16:18 2010
2010-11-23 15:16 CST Copyright (c) 1982, 2005, Oracle. All rights reserved.
2010-11-23 15:16 CST Load completed - logical record count 6.
2010-11-23 15:16 CST DEBUG: Completed executing SQL loader
# 4  
Old 11-23-2010
Hopefully I'm not over simplifying things...

sqlplus... | sed "s/^/$(date) /g" >> logfile

That says, replace the beginning of the line with the current date and a space.

Last edited by purdym; 11-23-2010 at 10:24 PM.. Reason: Changed to append the log file.
# 5  
Old 11-23-2010
Thank you, it worked for sqlldr as it is single statement ..
but for sqlplus am using something like below.. where should i include the sed.

sqlplus -s user/pass@Db <<EOF
select * from asd;
select * from werw;
EOF
# 6  
Old 11-23-2010
Try:

Code:
sqlplus -s user/pass@Db <<EOF | sed "s/^/$(date) /g" >> logfile
select * from asd;
select * from werw;
EOF

# 7  
Old 11-23-2010
One issue with the sed solutions posted up to now: all output from your sqlplus command will be logged with the same time (the time the command was invoked).

This little function should log the time each line in output, which you may find much more usefull in debugging those slow SQL statements:
Code:
logoutput()
{
   while read line
   do
      echo $(date) "$line" >> logfile
   done
}
 
#Example usage:
sqlplus -s user/pass@Db <<EOF | logoutput
select * from asd;
select * from werw;
EOF

Edit - Another thing to be aware of is the return status of your sqlplus command will be lost (replaced with that of sed or logoutput), so ensure you don't rely on $? in the script following the sqlplus call

Last edited by Chubler_XL; 11-23-2010 at 11:31 PM.. Reason: Added warning about return status
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Wrong output when writing to file

Hello, I am having problem while redirecting output to a file where as on console output is proper. for dir in */; do printf "%s, " "$dir"; ls -m "$dir"; echo; done > output.txt Output of above command is coming in single line but when i am redirecting output to a file, single line i... (10 Replies)
Discussion started by: Manoj Rajput
10 Replies

2. Shell Programming and Scripting

Getting output with sed without writing to a file

HI I am trying to grep 3 characters from hostname and append a character at the end. I tried as in the following: root@abag3:~# hostname | cut -c1-3 hyu Now I am trying to append "g" at the end of this output as in the following. root@abag3:~# hostname | cut -c1-3 | sed -s... (4 Replies)
Discussion started by: Priya Amaresh
4 Replies

3. Shell Programming and Scripting

Writing output to a file in columns

Hi I am trying to write output to a file in columns I have file in the follwoing: # cat file abc def # I am trying to write next output as like # cat file abc 123 def 345 # :mad: (6 Replies)
Discussion started by: Priya Amaresh
6 Replies

4. Shell Programming and Scripting

Sqlplus inside shell script writing to tmp file

Hi, facing an issue while calling sqlplus inside shell script. It for some reason goes to tmp file to write something and i get error as permission denied as i dont have access there. ANy idea why sqlplus writes in /tmp and how to change or stop this ? (2 Replies)
Discussion started by: rushikeshs
2 Replies

5. Shell Programming and Scripting

Writing the output of set -x into Log files

Hi Guys, I am using set -x in my script to track the flow of the script. But if i want to write the output of the set -x into a log file, how do i do it? Thanks, Ajay (3 Replies)
Discussion started by: Ajay Venkatesan
3 Replies

6. UNIX and Linux Applications

Please help: Oracle gqsql or sqlplus output format like mysql

On psql select titolo,lingua from titolo where titolo ~* 'brivid'; titolo | lingua ------- + ------ Brivido | 1 On Sqlplus/gqsql SQL> select titolo,genere,anno,lingua from titolo where titolo like '%rivid%'; TITOLO... (6 Replies)
Discussion started by: Linusolaradm1
6 Replies

7. Shell Programming and Scripting

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. #!/bin/ksh export USAGE="USAGE: `basename $0` -e <DBUSER> <DBPASSWD> <TNSNAME>" if ; then ... (2 Replies)
Discussion started by: WhoDatWhoDer
2 Replies

8. Shell Programming and Scripting

Dynamic output file generation using a input text file with predefined output format

Hi, I have two files , one file with data file with attributes that need to be sent to another file to generate a predefined format. Example: File.txt AP|{SSHA}VEEg42CNCghUnGhCVg== APVG3|{SSHA}XK|"password" AP3|{SSHA}XK|"This is test" .... etc --------- test.sh has... (1 Reply)
Discussion started by: hudson03051nh
1 Replies

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

10. UNIX for Dummies Questions & Answers

File Format issue: Output of sqlplus

Hi, I am using a query like below in my shell script : { { echo "set echo off" echo "set head off" echo "whenever sqlerror exit -1; select NUMBER ||','|| FNAME ||','|| LOC ||','|| ... (2 Replies)
Discussion started by: deepakgang
2 Replies
Login or Register to Ask a Question