Link to home
Start Free TrialLog in
Avatar of beaudoin_n
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
ASKER CERTIFIED SOLUTION
Avatar of seazodiac
seazodiac
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry, I think you have another mistake:

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
Avatar of beaudoin_n
beaudoin_n

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
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,in3,in4,in5,io1,io2,io3,io4,io5,io6);
end;
/

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you post the definition of your procedure ?

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