Link to home
Start Free TrialLog in
Avatar of cringleb
cringleb

asked on

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

SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cringleb
cringleb

ASKER

The answer would have been set linesize and also, select <field>||','||<field to achieve comma delimted output
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