Help required to pass the parameter

Shell Programming and Scripting
1

i am calling a pl/sql procedure through a shell script, there is one IN and 2 OUT parameter required to pass to the procedure to execute..

My procedure is XX_CITIDIRECT_EXP_PKG.main_proc and In parameter is p_period which I wanto to pass 'MAY-06'.

Can anyone figure out, whats is wrong here

#!/bin/ksh
setenv TSTAMP1 "'MAY-06'"
sqlplus -silent apps/apps <<EOF
variable p_period varchar2
variable l_errbuf varchar2
variable l_errcode varchar2
execute XX_CITIDIRECT_EXP_PKG.main_proc(p_period =>$TSTAMP1,errbuf => :l_errbuf,retcode => :l_errcode);
exit;
EOF

Thanks in advanec

2

in function or procedure unix variables should be passed like '$valiable_name'
not $variable_name.Try this

3

then could you tell me then how the MAY-06 will be passed in present case

execute XX_CITIDIRECT_EXP_PKG.main_proc(p_period =>'MAY-06',errbuf => :l_errbuf,retcode => :l_errcode);

but this is erroring out

4

Can you run in this way and make sure the same block is getting executed in sqlplus without error.If you get any error then paste it here.

sqlplus -silent apps/apps <<EOF
DECLARE
p_period         VARCHAR2;
l_errbuf         varchar2;
l_errcode        varchar2;

BEGIN
XX_CITIDIRECT_EXP_PKG.main_proc(p_period =>'MAY-06',errbuf => :l_errbuf,retcode => :l_errcode);
END;
/
EXIT;
EOF

and passing parameter as unix variable add this statement in shell script
x=`echo "'MAY-06'"`
and when you call that procedure pass value as p_period =>'$x'

5

#!/bin/ksh

TSTAMP1="'MAY-06'" <=== The problem is right here

sqlplus -silent demo/demoyou12@ctrmad1 <<EOF
variable p_period varchar2
variable l_errbuf varchar2
variable l_errcode varchar2
execute XX_CITIDIRECT_EXP_PKG.main_proc(p_period =>$TSTAMP1,errbuf => :l_errbuf,retcode => :l_errcode);
exit;
EOF