Obtain .csv compatible output from PLSQL run from a Korn Shell script against ORACLE 10g database

Running the following kornshell script
# IR.ksh - Korn Shell script producing IRMetrics.results using IRMetrics.sql;
## Set oracle path
################################################################
#
export ORACLE_HOME=/oracle/base/oracle/product/10.2.0/client_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export BASEPATH=/opt/perl/bin:/opt/java1.3/bin:/usr/local/bin:/usr/ccs/bin:/usr/bin:/usr/contrib/bin:/opt/hparray/bin:/opt/nettladm/bin:/opt/upgrade/bin:/opt/fcms/
export ORAPATH=~/oracle:$ORACLE_HOME/bin:$ORACLE_HOME/lib:$BASEPATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
export SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export NLS_LANG=american_america.UTF8
export ORACLE_SID=imagec1
export PATH=$PATH:$ORAPATH
#
OIFS=$IFS
IFS=,
#
#
sqlplus -s /nolog <<EOF 1> IRMetrics.results
      connect Imaging_history/zqmn58s2@imagec1
      set serveroutput on
      set hea off pagesize 0 feedback off
      set trim on
      set und off
      set COLSEP ","
      Set RECSEP wrap
      @IRMetrics.sql
EOF
#
     tmp2=`wc -l IRMetrics.results`
     if [[ $tmp2 = 0 ]]; then
      echo "ERROR EXIT IRMetrics.results file has 0 rows"
      exit
     fi
     echo $tmp2 "Records were created for the IR Metrics"
#  
IFS=$OIFS
I find I don't have the correct "SET" options defined to obtain a.csv compatible output for IRMetrics results. Would you please help with this? The SQL code is pased below.
Thank you for your trouble,
Sincerely,
Christine
Select *
From (SELECT user_action, from_business_area, from_queue, to_queue, 
         (CASE 
           when US_USERNAME in ('tpela', 'tpkdj', 'satso', 'sahaw', 'mcjg2', 'sasln') then  'TAP Supervisor'
           when US_USERNAME in ('SACS2') then 'Corr Ass Sup'
           when US_USERNAME in 
                ('sabac','sasps','tpsls','sadaj','sagw2','sanam','samsj','tpcmn','sacts','sajfs','saslp','saas3',
                 'acdkb','sarh2','fplrk','xisajmm','sahlm','sadjm','tpgll','sanjm','sambp','sajms','samsw','tpbmw',
                 'saeja','saalb','sarlb','sacjc','saejm','sakjn','sagjp','saeks','sads2','savs2','sajps','sades', 'salw2',
                 'salb2','samkp','tpmd3','satid','bpvdg','rpslh','gppli','sakrm','sarjn','saacp','rpdbr','tpgs2', 'sads3', 'rpsat',
                 'sapaa','sakmb','samc3','samid','tplfd','woskg','sajrh','sajpj','saejm','sawp2','ccjap','saons', 'sadjs',
                 'gpmc2','fpsmg','rpvle','sakjg','rpmah','saebh','gpdj5','saclm','sacdm','bpkm2','tpjtr', 'rpmas', 'rpjls') then 'TAP Staff'
           when US_USERNAME in
                ('rpmha', 'tprrc', 'tpsd2', 'tpjh2', 'saleh', 'tpggl', 'colml', 'tpdap', 'tpben', 'spmfr', 'tpdlr', 'cogs2', 'lpsvs', 'tpjav')
                then 'Corr Staff'
           else 'Ignore'
         END) as ORG,
         (CASE
           when  (      CAST(irb.ACTION_TIME AS DATE) >= CAST('14 Nov 2008' AS DATE)
                    AND CAST(irb.ACTION_TIME AS DATE) <=  CAST('12 Dec 2008' AS DATE) ) then 'Pre Super Q'
           when  (      CAST(irb.ACTION_TIME AS DATE) >= CAST('13 Dec 2008' AS DATE)
                    AND CAST(irb.ACTION_TIME AS DATE) <=  CAST('10 Jan 2009' AS DATE) ) then 'Post Super Q'
           else 'Ignore'
          END) as TMF
      FROM imaging_history.ir_base_mv irb
      WHERE         (   CAST(irb.ACTION_TIME AS DATE) >= CAST('14 Nov 2008' AS DATE)
                        AND CAST(irb.ACTION_TIME AS DATE) <=  CAST('10 Jan 2009' AS DATE) 
                    )
             AND
                    (   IRB.FROM_BUSINESS_AREA in ('TA Processing', 'Correspondence')
                      OR IRB.TO_BUSINESS_AREA in ('TA Processing', 'Correspondence')
                    )
             AND    (   IRB.From_Function in ('Processing')
                      OR IRB.To_Function  in ('Processing')
                    )
     )             
;

Open in new window

cringlebAsked:
Who is Participating?
 
woolmilkporcCommented:
Hi,
you should set a linesize big enough to hold all your columns, so that the ' , ' doesn't get missing at a line wrap.
e.g. set linesize 32767
which is the max.
 
HTH
wmp
 
 
0
 
flow01Commented:
What is the problem you encounter ?
Do you get output, what is wrong with the output , what do you mean with compatible ?
0
 
cringlebAuthor Commented:
The answer would have been set linesize and also, select <field>||','||<field to achieve comma delimted output
0
 
woolmilkporcCommented:
Hello Christine,
you get comma-delimited output with set colsep "," , the setting you already  had in your question, so I didn't need to mention.
The only difference to the version in your last comment is that the output will be seamlessly concatenated there, which ist, at least for processing with MS EXCEL, not necessary at all.
Anyway, thanks for the points,
à bientôt
Norbert
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.