UNIX for Advanced & Expert Users

Hello Guys,

This message is somewhat relates with last thread. But I need to re-write thing. I start over a little. I am stuck now and need your help.

Here is my script-

Code:

#! /bin/ksh
export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2
/opt/oracle/app/oracle/product/9.2/bin/sqlplus -s ccare/ccare@ccbs <<EOF
@myquery.sql

EOF

Myquery.sql output a COUNT of some raws. I just wanted to input those count (only numbers) to a file. And every time I run it should append in the file.

Your help pls.

Would setting "spool" to a list file in your sql script not suffice?
Then of course you would have to extract what you want to append elsewhere...

Hi VBE,

can you please show me exact way.

Much appreciated..

What is in your sql script?

---------- Post updated at 16:58 ---------- Previous update was at 16:55 ----------

e.g. at the beginning of script

Code:

spool /tmp/my_output.lst
set heading off
set feedback 0
set verify off
set auto off
set echo off


here is the sql request....
/

spool off
!echo "Spool file generated..."
clear buffer
EXIT

Hi VBE,
it is count statement in SQL script.
select count(*) from abc where xyz<>2;

---------- Post updated at 07:05 AM ---------- Previous update was at 06:43 AM ----------

VBE, I tried your script it is executed and working almost. Here is my script that i ran-
#! /bin/ksh
export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2
/opt/oracle/app/oracle/product/9.2/bin/sqlplus -s abc/abc@mydb <<EOF
spool /mycount.txt
set heading off
set feedback 0
set verify off
set auto off
set echo off

@myquery.sql
spool off
!echo "Spool file generated..."
clear buffer
EXIT

EOF



Here was the output
[aix2][/]# ./myquery.sh

0
Spool file generated...

[aix2][/]# cat mycount.txt

0
[aix2][/]#

I plan to put he script in crontab to run every 10 minutes. And hence I need the sql output in same file like below:
Date Run count
2010-11-12 06:00 0
2010-11-12 06:10 10
2010-11-12 06:20 3

Please say how can I do it??

---------- Post updated at 11:36 AM ---------- Previous update was at 07:05 AM ----------

Hi Guys,

I need to spool the results in same file just after 10 minutes. I will put that script in crontab;

How to modify the script accordingly?

You may want to try the OS's redirection capability here, instead of spool -

Code:

...
/opt/oracle/app/oracle/product/9.2/bin/sqlplus -s abc/abc@mydb <<EOF >> /mycount.txt
...

Unfortunately, you are still on Oracle 9i, which is an old version.

From Oracle 10g Release 2 onwards, the spool command comes with an "append" option that appends data to a spool file.

Prior to that version, the spool command creates the spool file every time it is invoked.

HTH,
tyler_durden