I am almost new to Oracle, been a decade since I last worked on it. I have query over multiple tables. I need to run the query daily and get a csv file out of it. How sure how to do it. Can anyone please help? In the csv file I need no headers etc. just comma separated rows.
I found this link which is very helpful, however, no matter what I try, the output file shows the query too. How do I make sure the output file has just the data and not the query?
Edited by: 857833 on May 10, 2011 9:00 AM
For sqlplus set echo off turns off the statement echo, but only when the sql is run from a file (@<file> or start <file>).
For SQLs at the SQL> prompt, you'll still get the statement in the spool file :(
So you are saying if I run the query at the sql prompt as from file, it might work? I will try that.
However, I doubt what you are saying is correct, some one somewhere would've mentioned it? But I have no knowledge on this. Just a guess.
I appreciate your response. This thing is driving me nuts. It is so simple and we can generate text files left and right all day long in mssql server. Too bad I couldn't find a comparatively simple solution in Oracle. :(
This is getting very frustrating. Below is what I type in my sql plus window.
set colsep ','
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off
This creates the output file, where first line is @"FILEWITHQUERY.SQL"
then shows the result set rows, the last row in the file is "spool off"
Can you please show me an example that works for you? I never imagined generating a text file from tables is this hard. :((