[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-17
4
Medium Priority
?
826 Views
Last Modified: 2013-12-21
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

0
Comment
Question by:cringleb
  • 2
4 Comments
 
LVL 21

Assisted Solution

by:flow01
flow01 earned 60 total points
ID: 23662149
What is the problem you encounter ?
Do you get output, what is wrong with the output , what do you mean with compatible ?
0
 
LVL 68

Accepted Solution

by:
woolmilkporc earned 1440 total points
ID: 23690666
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
 

Author Comment

by:cringleb
ID: 23873494
The answer would have been set linesize and also, select <field>||','||<field to achieve comma delimted output
0
 
LVL 68

Expert Comment

by:woolmilkporc
ID: 23873729
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question