Shell Programming and Scripting

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:

Code:

#!/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.

Code:

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.

Code:

set linesize 3500
set trimspool on

You can combine them both in one line:

Code:

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:

Code:

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,