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