piping oracle output to a file?

satnamx · July 23, 2006, 9:46am

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

ranj1 · July 24, 2006, 3:03am

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

Dhruva · July 24, 2006, 4:09am

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

satnamx · July 24, 2006, 5:20pm

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

ragha81 · July 28, 2006, 12:02pm

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

samuelc · May 4, 2007, 3:09am

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.

tmarikle · May 4, 2007, 10:57am

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

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

gauravah · March 17, 2008, 3:33am

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

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

Franklin52 · March 17, 2008, 5:47am

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:

awk '/----.*/{getline;print;exit}' testing1.txt

Regards

BhawanaAggarwal · March 17, 2008, 7:34am

Hi Gauravah,

U should try the following to display only output without dispalying oracle messages

set heading off
set lines 500
set trimspool on
set trimout on
set echo off
set pagesize 0
set feedback off

jambesh · March 17, 2008, 11:28am

try sqlplus -s user/password@database --(Use of -s option suppress the Oracle login messages)

gauravah · March 19, 2008, 4:26am

thanks to everyone for help,
also I will be careful next time not to hijack anyone's post. sorry for that

kwliew999 · March 19, 2008, 5:26am

Sorry for posting some Oracle related question in this forum as I don't know if any other place that I can post the question. I am sure there are many of you familiar with Oracle database and can answer my question.
My DBA recently written a analytic script to perform a table space analyse as well as reform of indexes in order to have a better performance on some application processes. The scripts that written by him is as follow :

Analyze table GNMM_INTEREST_RATE_MASTER estimate statistics for table for all indexes for all indexed columns;
Analyze table PS_INTEREST_TRANSACTION_TEMP estimate statistics for table for all indexes for all indexed columns;
Analyze table PSDT_APL_TRANSACTION_BREAKUP estimate statistics for table for all indexes for all indexed columns;
Analyze table PSMT_CLEARANCE_MASTER estimate statistics for table for all indexes for all indexed columns;
Analyze table PSDT_CLEARANCE_DETAILS estimate statistics for table for all indexes for all indexed columns;

I can run the script on AIX platform with Oracle priviledge. And the application process run after the script will have a very much improve performance.

I have also found another scripts written by some other DBA from other company who claimed that is the same as the one I stated above. The scripts are as below :

create or replace
procedure BFN_ANALYZE_INT_ACCTABLES is

Begin
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'gnmm_interest_rate_master',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PS_INTEREST_TRANSACTION_TEMP',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PSDT_APL_TRANSACTION_BREAKUP',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PSMT_CLEARANCE_MASTER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats(ownname => 'PROD',
tabname => 'PSDT_CLEARANCE_DETAILS',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
END BFN_ANALYZE_INT_ACCTABLES ;

I found that the above script doesn't have the same effect as the previous one which will improve performance of application processes.

Please advise whether the 2 scripts are similar or any differences that limited the performance?

Thanks in advance.

Franklin52 · March 19, 2008, 5:08pm

kwliew999

As mentioned above, if you have a question don't hijack a thread but start your own thread.

Regards