Help required to pass the parameter - Shell Programming and Scripting

u263066 · #1 (**permalink**) 08-10-2006

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

HTML Code:

#!/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

Dhruva · #2 (**permalink**) 08-10-2006

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

u263066 · #3 (**permalink**) 08-10-2006

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

Dhruva · #4 (**permalink**) 08-10-2006

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.

Code:

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'

lchi2000 · #5 (**permalink**) 08-11-2006

#!/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

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
Pass parameter into script	alfredo	Shell Programming and Scripting	2	04-08-2008 06:40 PM
how can i pass parameter with spaces to csh script	umen	Shell Programming and Scripting	1	03-19-2008 08:33 AM
PASS parameter to AWK	unisam	UNIX for Dummies Questions & Answers	2	05-14-2004 06:51 AM
Pass Parameter to Another Script	rvprod	UNIX for Dummies Questions & Answers	4	04-05-2002 09:07 AM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode