piping oracle output to a file?

Tags
shell scripts

 
Thread Tools Search this Thread
# 1  
Old 07-23-2006
piping oracle output to a file?

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
# 2  
Old 07-24-2006
spool

Why dont you spool the output to a file
Code:
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

# 3  
Old 07-24-2006
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
# 4  
Old 07-24-2006
Thanks!

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
# 5  
Old 07-28-2006
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..
# 6  
Old 05-04-2007
Please explain this code snippet to me

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.
# 7  
Old 05-04-2007
Quote:
Originally Posted by samuelc
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.
Code:
{
  echo "...
} | sqlplus

basically collects all of the "echo" command's output as a group and they are piped through to Oracle's sqlplus command processor. So it behaves just like running sqlplus interactively and you typing in those commands.

Code:
>> $root_dir/$csv_file1

directs the output from the SQL commands to a file on the UNIX server. The path and filename are in variables $root_dir and $csv_file.

Ultimately, the SQL is building a csv (comma delimited) file that will be used for, presumably, as an input to another system.

Thomas

|
Thread Tools Search this Thread
Search this Thread:
Advanced Search

More UNIX and Linux Forum Topics You Might Find Helpful
Help on parsing Oracle RMAN output for string and print sections of a file newbie_01 UNIX for Dummies Questions & Answers 1 02-13-2016 04:37 AM
Oneliner ---split string to character by piping shell output to perl yifangt Shell Programming and Scripting 6 01-07-2013 11:46 AM
Abnormality while piping tr command output to sed chidori Shell Programming and Scripting 9 12-19-2012 10:16 PM
Piping output of ls to a text file LinuxNubBrah Shell Programming and Scripting 9 12-02-2011 12:02 PM
need help piping the output from an app... uh, yeah... ninjaaron Shell Programming and Scripting 6 10-02-2011 04:40 PM
Piping output from a command into bash script akindo Shell Programming and Scripting 2 09-01-2010 12:12 AM
Need help with a sh script to spool directory and modify the output (Oracle cnt file) exm Shell Programming and Scripting 6 06-24-2009 02:26 PM
Piping output of "top" to a text file safraser Fedora 13 05-21-2009 05:13 PM
Piping and assigning output to a variable in Perl Raynon Shell Programming and Scripting 10 05-13-2009 07:45 PM
oracle query output in excel file yabhi_22 Shell Programming and Scripting 4 04-29-2009 11:01 AM
piping output from PHP file into variable daydreamer Shell Programming and Scripting 2 12-08-2008 09:53 PM
piping output of tail running in background anuramdas Shell Programming and Scripting 4 07-27-2007 03:20 PM
piping output to echo A1977 Shell Programming and Scripting 3 11-01-2006 08:58 AM
piping the output of find command to grep 435 Gavea UNIX for Dummies Questions & Answers 1 10-05-2006 05:48 PM
Piping output to while read Ultimodiablo Shell Programming and Scripting 3 11-26-2005 10:38 PM