Shell Programming and Scripting

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.

This is a sqlplus problem, not unix.

Place this above the select statement:

Code:

COL FIRST_NAME       format a8 trunc   head 'fname'
COL LAST_NAME        format a8 trunc   head 'lanme'
COL TNUMBER          format a8 trunc   head 'tnum'
COL CAMPAIGN_NAME    format a8 trunc   head 'cname'
COL TEAM_TITLE       format a8 trunc   head 'ttitle'
COL PRODUCT_TYPE_ID  format a8 trunc   head 'prod typ'
COL PRODUCT_ID 		 format a8 trunc   head 'prod id'
COL PRODUCT_NAME     format a8 trunc   head 'prod name'
COL UPDATED_DT       format a8 trunc   head 'prod dt'

I gave each column 8 characters (a8). You get to correct that. If any columns are numeric us format 9999 - with enough 9's to cover the width of the column. OTherwise you get ##### instead.

the head clause is also yours to edit. The number of chars in the head clause needs to be no longer than the format statement allows the column to be.

Consider learning about sqlplus.