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
beaudoin_nAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
seazodiacConnect With a Mentor Commented:
you have to pass the exact number of the parameter you defined in the stored procedure definition as well as the RIGHT data type.

for example:

to run your sp:

exec stored_proc_name (in1,in2,in3,'in4',in5);
0
 
seazodiacCommented:
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
0
 
beaudoin_nAuthor Commented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
ishandoCommented:
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;
/

0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
When you call a stored procedure (or function) you must provide a value (or a variable) for each "in" parameter (unless some or all of the "in" parameters have default values).  You must also provide a variable (not a literal value) for each "inout" parameter.

If you post the text or the description (that contains the list of the parameters with their datatypes) of your stored procedure here, we can give you the exact syntax you need.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
For 'in out' variables, oracle needs a place to store the results of the 'out' part of the equation.

In SQL*Plus you can declare variables to be used for this.  The following is a quick example of this in SQL*Plus:
=======================================================================
create or replace procedure junk( myvar in out varchar2) as
begin
      myvar := 'Hello World';
end;
/

show errors

var myvar varchar2(50);

exec junk(:myvar);

print myvar
0
 
pratikroyCommented:
Can you post the definition of your procedure ?

Or

DESC stored_proc_name

on the SQL Plus command prompt, and post the results here
0
 
slightwv (䄆 Netminder) Commented:
In memory of seazodiac:  Give me the points!!!!!!

My suggestion:  3 way split.  seazodiac, markgeer, slightwv
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.