hi all,
have a ksh script which connects to a database and runs a sql and dumps it to a '.csv' file. The problem is the result is in multiple rows with long spaces in between when it should be just a single line and this screws up the format in the '.csv' file.

script is :

Code:

#!/bin/ksh
export SQLPLUS=/appl/oracle/product/9.2.0.4.64/client/bin/sqlplus;
export mydate=`/usr/bin/date +%d%m%Y_%H_%M`;

$SQLPLUS -s $1/$2@$3<<EOF

set head off
set feedback off
set lines 300
set pages 300

spool out_$mydate.csv

 SELECT 'name ', ',',
        'Number', ',',
        'Name', ',',
        'name', ',',
        'Product', ',',
        'Product ID', ',',
        'Product Name', ',',
        'Date'
 from dual;

SELECT  (users.FIRST_NAME || ' ' || users.LAST_NAME) || ',',
              users.TNUMBER || ',',
              cam.CAMPAIGN_NAME || ',',
              team.TEAM_TITLE || ',',
              pSales.PRODUCT_TYPE_ID || ',',
              pSales.PRODUCT_ID || ',',
              product.PRODUCT_NAME || ',',
              pSales.UPDATED_DT
FROM tables names
WHERE ...... ;
spool off
EOF

this script is meant to write out the heading i.e the first select and then put data under those headers but since the data has long spaces in it, the format is messed up.

This is what gets written out to the '.csv' file :


name , Number , Name , name, Product , Product ID , Product Name , Date

sys admin, t12345,
my plan 08,
Administrators, 3, 1005,

myproduct, 12-AUG-09


when i was expecting to see :


name , Number , Name , name, Product , Product ID , Product Name , Date

sysAdmin, t12345,my plan 08, Administrators, 3, 1005, myproduct, 12-AUG-09


so all data would be a single row in the 'csv' file rather then multiple rows in different columns with large spaces.

any suggestions ???

thanks.