We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

Medium Priority
876 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

Comment
Watch Question

flow01IT-specialist
CERTIFIED EXPERT
Commented:
What is the problem you encounter ?
Do you get output, what is wrong with the output , what do you mean with compatible ?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
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
 
 

Author

Commented:
The answer would have been set linesize and also, select <field>||','||<field to achieve comma delimted output
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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
 
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.