SQL query in UNIX script - output in flat file

Hi,

I never did this before... what I want to do is execute a SQL query from a unix script and redirect sql query's output to a flat file (comma separated one) without the header info (no column names). I would also want not to print the query's output to the screen.

snapshot of my script:

#!/bin/csh -f
setenv AIM_PSWD `awk -F= '/AIM_PSWD/ {print $2}' < $AIM_CONFIG`
sqlplus $AIM_PSWD  << EOF > sql.out
select * from <table>
where <condition>;
EOF

Problem is that sql.out contains all other things that I do not want.. it contains column names after every certain no of records, contains info such as "connected to oracle database" and all that...

Is there any other way to do this?
I used SQLLDR to load data from flat file to the database table. Can I use SQLLDR for reverse action - from table to file? Syntax?

Appreciate for your help.

sqlplus -s $AIM_PSWD  << EOF > sql.out
 set feed off
 set head off
 set pages 0
select * from <table>
where <condition>;
EOF

try that.

Almost there.... thanks
only one problem... each row in output file is distributed in multiple lines...
How can I get each row in only one line... 2nd row in 2nd line...so on?

Most likely your linesize is low. Set it to a high value. Also set trimspool.

set linesize 3500
set trimspool on

You can combine them both in one line:

set linesize 3500 trimspool on

I hope I am not asking too much...
I used LTRIM to remove trailing whitespaces... but output file has fixed column width.
I want to embed "," between columns to make it a csv file and do not want any trailing white spaces either. Any idea how to do it?

You can set colsep to comma to generate a CSV output:

set colsep ,

How to set column size in such a way that it will be equal to the number of characters present in the column (excluding trailing white spaces)? So instead of fixed column width, it will be a variable one.
For eg:

,ELLS ,KRISTIN ,

instead, it should look like
,ELLS,KRISTIN,

One approach would be merging fields using || separated by comma ',' :

select field1 || ',' || field2 || ',' || field3 from table_name;

Thank you guys for all your help...

---------- Post updated at 04:19 PM ---------- Previous update was at 01:27 PM ----------

One last question:
I am able to create a csv file now, but when I open it in windows (double click)... leading zeros in certain columns are missing... may be because of text to number conversion..
How can I avoid this? Is it something that I can specify in sqlplus while fetching the records from the table and save it in the csv file?

I assume you are opening your CSV in Microsoft Excel.

If yes, by default the cell format will be general number and it will strip leading zeros.

You have to select the range of cells and change the format manually in order to view the leading zeros.

Thanks Yoda..
Manually I can do that....
But I need to do it as a part of my automation process... any options in SET commands in sqlplus??

No, there is no such option.

As I already mentioned it is behavior of your spreadsheet application.

If it is just numeric with leading zero, then it will strip leading zeros. Or you might have to put a symbol or character as prefix to preserve the zeros.

But I don't think that is what you want :rolleyes:

I see...
I guess no... I do not want to put any symbols....
Thanks anyway, Yoda.

If you prefix the field with a single quote it may preserve the leading zeros. It does in Open Office, in any case, so might work in Excel.