beaudoin_n
asked on
Executing a stored procedure with in out parameters
Hi,
I am just trying to execute stored procedures that are already built, and i get error messages when i try to execute.
I have several IN parameters that i assign, but my IN OUT parameters i don't have the values for, or i don't think they are relevant.
So i go in sqlplus and type
exec stored_proc_name (in1,in2,in3,'in4',);
It tells me that there are not enough arguments
My IN OUT are like this
inout1 integer
inout2 integer
inout3 varchar 2
inout4 integer
inout5 integer
so when i try with NULL values for the integers and ' ' for the varchar it gives me this error
ORA-06550: line 1, column 56:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 61:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 66:
PLS-00363: expression ' ' cannot be used as an assignment target
ORA-06550: line 1, column 70:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 75:
PLS-00363: expression ' NULL' cannot be used as an assignment target
When i try with 0 instead of NULL i get this error
ORA-06550: line 1, column 56:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 58:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 60:
PLS-00363: expression ' ' cannot be used as an assignment target
ORA-06550: line 1, column 64:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 66:
PLS-00363: expression '0' cannot be used as an assignment target
What is the correct syntax to run a stored procedure ?
Thanks
I am just trying to execute stored procedures that are already built, and i get error messages when i try to execute.
I have several IN parameters that i assign, but my IN OUT parameters i don't have the values for, or i don't think they are relevant.
So i go in sqlplus and type
exec stored_proc_name (in1,in2,in3,'in4',);
It tells me that there are not enough arguments
My IN OUT are like this
inout1 integer
inout2 integer
inout3 varchar 2
inout4 integer
inout5 integer
so when i try with NULL values for the integers and ' ' for the varchar it gives me this error
ORA-06550: line 1, column 56:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 61:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 66:
PLS-00363: expression ' ' cannot be used as an assignment target
ORA-06550: line 1, column 70:
PLS-00363: expression ' NULL' cannot be used as an assignment target
ORA-06550: line 1, column 75:
PLS-00363: expression ' NULL' cannot be used as an assignment target
When i try with 0 instead of NULL i get this error
ORA-06550: line 1, column 56:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 58:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 60:
PLS-00363: expression ' ' cannot be used as an assignment target
ORA-06550: line 1, column 64:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 66:
PLS-00363: expression '0' cannot be used as an assignment target
What is the correct syntax to run a stored procedure ?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Does not seem to work
I have 5 in parameters then 6 in out parameters
If i put only the 5 IN without any types i still get the mismatched parameters.
If i put all of them i get an error on my in out varchar
ORA-06550: line 1, column 64:
PLS-00201: identifier 'A' must be declared
If i leave that same in out varchar empty i get this error
ORA-06550: line 1, column 64:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "," to continue.
Thanks
Does not seem to work
I have 5 in parameters then 6 in out parameters
If i put only the 5 IN without any types i still get the mismatched parameters.
If i put all of them i get an error on my in out varchar
ORA-06550: line 1, column 64:
PLS-00201: identifier 'A' must be declared
If i leave that same in out varchar empty i get this error
ORA-06550: line 1, column 64:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( - + case mod new not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for "," to continue.
Thanks
Try:
declare
in1 <type> := <value>;
in2 <type> := <value>;
in3 <type> := <value>;
in4 <type> := <value>;
in5 <type> := <value>;
io1 <type>;
io2 <type>;
io3 <type>;
io4 <type>;
io5 <type>;
io6 <type>;
begin
store_proc_name(in1,in2,in 3,in4,in5, io1,io2,io 3,io4,io5, io6);
end;
/
declare
in1 <type> := <value>;
in2 <type> := <value>;
in3 <type> := <value>;
in4 <type> := <value>;
in5 <type> := <value>;
io1 <type>;
io2 <type>;
io3 <type>;
io4 <type>;
io5 <type>;
io6 <type>;
begin
store_proc_name(in1,in2,in
end;
/
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you post the definition of your procedure ?
Or
DESC stored_proc_name
on the SQL Plus command prompt, and post the results here
Or
DESC stored_proc_name
on the SQL Plus command prompt, and post the results here
In memory of seazodiac: Give me the points!!!!!!
My suggestion: 3 way split. seazodiac, markgeer, slightwv
My suggestion: 3 way split. seazodiac, markgeer, slightwv
when you pass variables into stored procedures, you don't quote them:
so try to run this:
exec stored_proc_name (in1,in2,in3,in4,in5);
make sure in3 is varchar type