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/o racle/prod uct/10.2.0 /client_1
export TNS_ADMIN=$ORACLE_HOME/net work/admin
export BASEPATH=/opt/perl/bin:/op t/java1.3/ bin:/usr/l ocal/bin:/ usr/ccs/bi n:/usr/bin :/usr/cont rib/bin:/o pt/hparray /bin:/opt/ nettladm/b in:/opt/up grade/bin: /opt/fcms/
export ORAPATH=~/oracle:$ORACLE_H OME/bin:$O RACLE_HOME /lib:$BASE PATH
export LD_LIBRARY_PATH=$ORACLE_HO ME/lib:$OR ACLE_HOME/ ctx/lib
export SHLIB_PATH=$ORACLE_HOME/li b32:$ORACL E_HOME/ctx /lib
export ORA_NLS33=$ORACLE_HOME/oco mmon/nls/a dmin/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@i magec1
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
# IR.ksh - Korn Shell script producing IRMetrics.results using IRMetrics.sql;
## Set oracle path
##########################
#
export ORACLE_HOME=/oracle/base/o
export TNS_ADMIN=$ORACLE_HOME/net
export BASEPATH=/opt/perl/bin:/op
export ORAPATH=~/oracle:$ORACLE_H
export LD_LIBRARY_PATH=$ORACLE_HO
export SHLIB_PATH=$ORACLE_HOME/li
export ORA_NLS33=$ORACLE_HOME/oco
export NLS_LANG=american_america.
export ORACLE_SID=imagec1
export PATH=$PATH:$ORAPATH
#
OIFS=$IFS
IFS=,
#
#
sqlplus -s /nolog <<EOF 1> IRMetrics.results
connect Imaging_history/zqmn58s2@i
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')
)
)
;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER