Hi All...

Does anyone know how to pipe the output of a "select" statement from a call to Oracle to a file?

ANy ideas woule be greatly appreciated!

Code is as below...

echo "producing CSV file 2..."
sqlplus -s $username/$password@$database<<EOF

set serveroutput on size 1000000
set verify off
set feedback off
set pagesize 0
VARIABLE vi_err NUMBER

SELECT empno||','||ename||','||job||','||mgr||hiredate||','||sal||','||comm||','||deptno FROM emp;
EXIT:vi_err
EOF

Why dont you spool the output to a file

If you want to use redirection to a file ..try this.Now testing.dat will have the sql output.

X=`sqlplus -s user/pwd@host<<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select * from table where rownum<5;
EXIT;
eof`

echo $X>testing.dat

Cheers lads! v useful tips.

I also found another way... as below.

{
echo "set pagesize 0"
echo "SELECT empno||','||ename||','||job||','||mgr||hiredate||','||sal||','||comm||','||deptno "\
"FROM emp;"
} | sqlplus -s $username/$password@$database >> $root_dir/$csv_file1

Kind Regards

Satnam

hi satnam.. is there a way not to mention the user name and pasword in the script. i have a requirement like this. i have to run a sql query using shell scripts. once the query is run, the results have to be stored in a data file. if any values are returned by the query, it should display an error msg to the user or else it has to exit. pls help me with this as i am totally new to shell scripting and i just started to learn..

hi, I am new to unix I have executed the following script :-

X=`sqlplus service/service@psmf.world <<eof
set serveroutput on;
set feedback off;
set linesize 1000;
select count(*) from table ;
EXIT;
eof`

echo $X>testing1.txt

.... and got the following output

vi testing1.txt
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Mar 17 07:57:45 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL>
COUNT(*)
----------
270

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production


I only want output 270 in the file. How can i get this output? Please give your inputs

If you have questions do not hesitate to ask but start your own thread and don't hijack old threads.
Having said that, with awk you can get the value as follow:

Regards