Output the SQL Query result to a File

UNIX for Advanced & Expert Users
1

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-

#! /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.

2

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

3

Hi VBE,

can you please show me exact way.

Much appreciated..

4

What is in your sql script?

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

e.g. at the beginning of script

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
5

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?

6

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

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