Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Executing a stored procedure with in out parameters

Posted on 2004-10-17
11
Medium Priority
?
77,524 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 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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 77

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

721 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