Passing Parms from UNIX ksh Shell to SQL*Plus

[gsdcrazy]

Gurus,

I have a UNIX ksh shell that initiates a SQL*Plus script. Whenever there are spaces in the parameter, the spaces are eliminated when the SQL*Plus script recieves/executes it. I have performed "echos" of the parameters just before initiating the SQL*Plus shell and just after. The parms look fine on the UNIX side. Is there a way to pass the parms and not have the spaces eliminated?

Below are some excepts from the UNIX shell:


#------------------------------------------------------------------------------
# Build report heading
#------------------------------------------------------------------------------

if [ $first_process_date -eq $second_process_date ] ; then
report_heading="'Agency Commission Balancing Report for $( echo $format_first_process_date ) for $( echo $prod_line ) Product Lines'";
else
report_heading="'Agency Commission Balancing Report for $( echo $format_first_process_date ) through $( echo $format_second_process_date ) for $( echo $prod_line ) Product Lines'";
fi

#------------------------------------------------------------------------------
# Run the sql program
#------------------------------------------------------------------------------

{
echo "${UID}"
echo "@${SMISQL_PATH}/agency_comm_bal_rpt.sql"
echo "${first_process_date}"
echo "${second_process_date}"
echo "${prod_line}"
echo "${monthid}"
echo "${report_heading}"
} | sqlplus -s

The "report_heading" is built within the shell. The "prod_line" can be entered as a shell parameter or is defaulted in the shell. Both lose the spaces when sent to the SQL*Plus script. The "prod_line" is just fine if it is a single word Product Line.

Below is part of the SQL*Plus script:

col prod_src_id for a15
col prod_line for a25
col stg_dollar for 999,999,999,999.99
col dm_dollar for 999,999,999,999.99
col diff for 999,999,999,999,999.99
set lines 150
set pages 100
set linesize 106
set termout off
set verify off
set echo off

define v_first_load_date = &1
define v_second_load_date = &2
define v_prod_line = &3
define v_monthid = &4
define v_report_heading = &5

ttitle center &v_report_heading skip 2

break on report
compute sum of stg_dollar on report
compute sum of dm_dollar on report
compute sum of diff on report

spool ${LOG_PATH}/NDWY9016.lst

select staging.prod_line
,staging.prod_src_id
,NVL(staging.dollar, 0) stg_dollar
,NVL(datamart.dollar, 0) dm_dollar
,NVL(staging.dollar, 0) - NVL(datamart.dollar, 0) diff
from (select /*+ use_hash(stg, dm) */
dm.prod_id
,dm.prod_line
,dm.prod_src_id
,stg.process_date
,sum(nvl(stg.total_premium, 0)
+ nvl(stg.commission_amount, 0)
+ nvl(stg.bonus_amount, 0)
+ nvl(stg.balance_payment_amount, 0)
+ nvl(stg.cost_of_insurance_amount, 0)) dollar
from (select /*+ parallel(a) */
a.process_date
,a.product_ext_id
,a.total_premium
,a.commission_amount
,a.bonus_amount
,a.balance_payment_amount
,a.cost_of_insurance_amount
from agency_commissions a
where (load_date = to_date('&v_first_load_date','YYYYMMDD')
or load_date = to_date('&v_second_load_date','YYYYMMDD'))
and commission_status = 'A'
) stg
,(select /*+ use_hash(p, pl) */
prod_id
,prod_src_id
,prod_line
from products p
,product_lines pl
where p.prod_line_id = pl.prod_line_id
and (pl.prod_line = '&v_prod_line'
or 'All' = '&v_prod_line')
) dm
where stg.product_ext_id = dm.prod_src_id
group by dm.prod_id
,dm.prod_line
,dm.prod_src_id
,stg.process_date
) staging
,(select /*+ parallel(ci) */
ci.prod_id
,ci.date_id
,sum(ci.comm_item_amt) dollar
from commission_items partition(comm_items_&v_monthid) ci
group by ci.prod_id
,ci.date_id
) datamart
where staging.prod_id = datamart.prod_id (+)
and staging.process_date = datamart.date_id (+)
and NVL(staging.dollar, 0) - NVL(datamart.dollar, 0) != 0

The Report Heading is centered with two lines after it, but the words are all jammed together. The "v_prod_line" gets no hits on any value with spaces in it.

Your help is greatly appreciated.

Thanks,
gsdcrazy

 

[sem]

The problem is in your DEFINE statement(s): you should quote parameters to prevent assigning only the first word.

...
define v_report_heading = '&5'
...

Regards, Dima

 

[gsdcrazy]

Dima,

Thanks so much. That almost did the trick. I also had to put quotes around the variable in the "ttitle" command:

ttitle center '&v_report_heading' skip 2

Otherwise, no title appeared at all.

Thanks again,
gsdcrazy

 

[dspare]

Another way would be to reference the environment variables within the SQL section --

sqlplus -s <<EOF
${UID}
@${SMISQL_PATH}/agency_comm_bal_rpt.sql ${first_process_date} ${second_process_date} ${prod_line} ${monthid} ${report_heading}

EOF

This method explicitly puts the spaces in the command line of the SQL script as it is done from within SQL*Plus. You are essentially writing the entire thing as a shell script so the previously define ksh variables get substituted in the right places. Additionally, when doing it this way, you can debug by using 'set -x' and running your script from within the 'script' command (see man pages). Then all shell substitutions will appear where they are used and you can see for yourself what the actual argument lists look like.