Format the output from sqlplus while writing to log file.

Shell Programming and Scripting
1

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

Can you give me an example of what you want to see.

3

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

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.

5

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

Try:

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

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:

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

8

Thank you very much, it worked but there are empty lines in the log as shown below

2010-11-23 21:27 CST DEBUG: Start executing test1.sql
2010-11-23 21:27 CST
2010-11-23 21:27 CST PL/SQL procedure successfully completed.
2010-11-23 21:27 CST
2010-11-23 21:27 CST DEBUG: Completed executing test1.sql
2010-11-23 21:27 CST DEBUG: Start executing SQL loader

9

Those empty lines were in the output for sqlplus too. But pretty easy to remove:

logoutput()
{
   while read line
   do
       [ -n "$line" ] && echo $(date) "$line" >> logfile
   done
}
 
#Example usage:
sqlplus -s user/pass@Db <<EOF | logoutput
select * from asd;
select * from werw;
EOF