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

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

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

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 all,
I am new to shell scripting and want to know how this code works.

{
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

Thanks in advance.

{
  echo "...
} | sqlplus

>> $root_dir/$csv_file1