Solved

PLS-00306: wrong number or types of arguments in call to

Posted on 2011-09-22
2
2,478 Views
Last Modified: 2013-12-19
Hi Gurus,

I'm trying to resolve this but in vain. I know its simple but not sure what;s wrong. Please advice


Proc when executed without giving the parameter names it works.
But when given i.e calling using => it fails.

Error report:
ORA-06550: line 10, column 3:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored

Please advice. Attached is the code.  
create or replace procedure proc1(i_name   IN varchar2,
                                  o_owner  OUT varchar2,
                                  o_type   OUT varchar2,
                                  o_id     OUT varchar2
                                  ) 
as
 v_owner  varchar2(30) := '';
 v_type   varchar2(30) := '';
 v_id  varchar2(30) := '';
begin
 select owner,object_type,data_object_id
  into v_owner,v_type,v_id
  from all_objects
 where UPPER(object_name) = UPPER(i_name);
  o_owner := v_owner;
  o_type  := v_type;
  o_id    := v_id;
end;
/

-- Test Case 1: Call Proc 
declare
 ip_name  varchar2(30) := '';
 v_owner  varchar2(30) := '';
 v_type   varchar2(30) := '';
 v_id     varchar2(30) := '';
begin
  ip_name := 'EMP';
  proc1(ip_name,
        v_owner,
        v_type,
        v_id       
        );
 dbms_output.put_line ('ip_name - '||ip_name);
 dbms_output.put_line ('v_owner - '||v_owner);
 dbms_output.put_line ('v_type - '||v_type);
 dbms_output.put_line ('v_id - '||v_id);
end;
/

-- test Case 2: Call proc FAILS

declare
 ip_name  varchar2(30) := '';
 v_owner  varchar2(30) := '';
 v_type   varchar2(30) := '';
 v_id     varchar2(30) := '';
begin
  ip_name := 'EMP';
  proc1(testname  => ip_name,
        testowner => v_owner,
        testtype  => v_type,
        testid    => v_id       
        );
 dbms_output.put_line ('ip_name - '||ip_name);
 dbms_output.put_line ('v_owner - '||v_owner);
 dbms_output.put_line ('v_type - '||v_type);
 dbms_output.put_line ('v_id - '||v_id);
end;
/

Open in new window

0
Comment
Question by:loginboy
2 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36584885
if you use the named parameter method, you must use the correct parameter names


change this...

 proc1(testname  => ip_name,
        testowner => v_owner,
        testtype  => v_type,
        testid    => v_id      
        );


to this ...


 proc1(i_name => ip_name,
        o_owner => v_owner,
        o_type => v_type,
        o_id => v_id      
        );
0
 
LVL 3

Expert Comment

by:cklautau
ID: 36584892
You have to use the parameters name defined on the procedure; try:
declare
 ip_name  varchar2(30) := '';
 v_owner  varchar2(30) := '';
 v_type   varchar2(30) := '';
 v_id     varchar2(30) := '';
begin
  ip_name := 'EMP';
  proc1(i_name  => ip_name,
        o_owner => v_owner,
        o_type  => v_type,
        o_id    => v_id      
        );
 dbms_output.put_line ('ip_name - '||ip_name);
 dbms_output.put_line ('v_owner - '||v_owner);
 dbms_output.put_line ('v_type - '||v_type);
 dbms_output.put_line ('v_id - '||v_id);
end;
/
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Processing of multiple cursor 6 45
help on oracle query 5 43
MULTIPLE DATE QUERY 15 76
Require data to appear on a single line 2 39
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

786 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