Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Executing a stored procedure with in out parameters

Posted on 2004-10-17
11
Medium Priority
?
77,621 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
8 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 172 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 164 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 164 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 78

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

877 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