Solved

Executing a stored procedure with in out parameters

Posted on 2004-10-17
11
77,397 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:beaudoin_n
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 43 total points
ID: 12334576
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 12334591
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
 

Author Comment

by:beaudoin_n
ID: 12334878
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 15

Expert Comment

by:ishando
ID: 12335069
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 41 total points
ID: 12337143
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 41 total points
ID: 12338780
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
 
LVL 9

Expert Comment

by:pratikroy
ID: 12346840
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 14112605
In memory of seazodiac:  Give me the points!!!!!!

My suggestion:  3 way split.  seazodiac, markgeer, slightwv
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question