Wednesday, March 28, 2012

problems in variable passing from shell to procedure

hi,
i need to passing the variable from shell script to procedure,but it does not work.the following are my shell and procedure.
job(){
sqlplus user/password@.orcl <<EOF
exec count_prize_proc($1,$2,$3)
EOF
}
if [ $# -eq 3 ] ;then
job
else
echo " EXAMPLEjobstart.sh 155 200 210 "
fi

create or replace procedure count_prize_proc
(t_play_code in varchar2,
t_begin_term in varchar2,
t_end_term in varchar2 )
IS

------------------
call_proc.sh 005 100 200
write win_prize Program
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 30 13:59:02 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

SQL> BEGIN count_prize_proc(,,); END;
*
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( ) - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum
variance execute multiset the both leading trailing forall
year month DAY_ HOUR_ MINUTE_ second TIMEZONE_HOUR_
TIMEZONE_MINUTE_ time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
-------------------
then i modified my shell
exec count_prize_proc("$1","$2","$3")

------------------
SQL> ERROR:
ORA-01741: illegal zero-length identifier

Any ideas/suggestions?
thanks advance

graceHi,

ORA-01741: illegal zero-length identifier is raised because, an attempt was made to use two double quotes ("") as an identifier.

An identifier must be at least one character long. Action: Insert at least one character between the double quotes in the identifier. If a blank identifier is required, specify a blank space between the double quotes (" ").sql

No comments:

Post a Comment