PLS-00306 Error: Wrong number of types of arguments in call to procedure

I have been working to resolve this for 2 days. I have a package
specification and body that compile successfully. I have a file
containing a call to the procedure that is in the package body. This file has the variables listed first and then the EXEC statement listed next. It is very long, so I won't take up space to print the whole thing here, but here is a glimpse of what it looks like:

VAR v_vis_c_fname VARCHAR2(10)
VAR v_vis_c_lname VARCHAR2(15)
[more variables listed here]...

EXEC load_names_p.pr_load_names (:v_vis_c_fname, :v_vis_c_lname, [more variables listed here]...)

I get the PLS-00306 error when I execute the command to call the
procedure. I am executing this file from SQL*Plus using the @call_procedures command (call_procedures is the name of the file that contains the code above.

I have checked over and over again to see if any of the variables in
the calling procedure file above are misspelled or missing. I even took
all the variables from the actual procedure in the package body, the
declaration section of the file above that calls the procedure, and the variables listed after the EXEC statement in the file above that calls theprocedure, put them in an excel worksheet, looked at them side-by-side, and they are all identical.

I also checked the data type of each variable in the declaration
section of the file above that calls the procedure and compared them with their associated columns in the database tables, and they are all correct.

The code in the procedure is rather simple at this point, but is many pages long, so I don't want to paste it all here. But suffice it to say that all this procedure does at this point is select values from tables into the variables that i've declared.

If all of my variables are listed correctly in the file above that calls
the procedure, and each of their data types is also correct, can
anyone think of any other reason why I would get a "PLS-00306 Error: Wrong number of types of arguments in call to procedure" error
message? What else could it be? I am at my wits end!

Thank you,
Alan

Per my last message... Here is what my procedure looks like (it's
too long to paste the whole thing, but it basically just repeats
what is below with different variables):

CREATE OR REPLACE PACKAGE BODY load_names_p AS
PROCEDURE pr_load_names (
v_vis_c_fname OUT emps.c_fname%TYPE,
v_vis_c_lname OUT emps.c_lname%TYPE,
[etc]...

IS
BEGIN
BEGIN
BEGIN
SELECT c_fname INTO v_vis_c_fname FROM emps
WHERE week_no=1 and dept='sales';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
BEGIN
SELECT c_fname INTO v_vis_c_lname FROM emps
WHERE week_no=1 and dept='sales';
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

[and so on...]
END pr_load_names;
END load_names_p;

Everything is on a local machine.

I would start by writing a PL/SQL test harness for the procedure rather than using SQL*Plus bind variables. Write a small PL/SQL block of the form:

declare
v_vis_c_fname emps.c_fname%TYPE;
v_vis_c_lname emps.c_lname%TYPE;
...
begin
load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
end;

Get that working, then selectively replace each variable with its bind equivalent until you can track down the problem. Also, make sure you are not inadvertantly using two different user IDs - e.g. you could be compiling the package under one ID but running it under a different one (which has its own version of the package).

Scorby, thanks for your help. I wrote the PL/SQL test harness as you suggested and got it to work:

declare
v_vis_c_fname emps.c_fname%TYPE;
v_vis_c_lname emps.c_lname%TYPE;
...
begin
load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
end;

Then I began putting colons in front of the variables in the call to the procedure:

load_names_p.pr_load_names (:v_vis_c_fname,:v_vis_c_lname,...)

There are 168 varibles in the parentheses. As you suggested, I kept adding the colons in front of the variables, I got an error message on the 140th variable, which is v_vis_inn2_rfb_pts.

load_names_p.pr_load_names (...:v_vis_inn1_rfb_pts,:v_vis_inn2_rfb_pts,v_vis_inn3_rfb_pts...)

When I executed this, I received:
SP2-0552: Bind variable "V_VIS_INN2_RFB_PTS" not declared.

I checked its spelling, etc, and can't find anything wrong with it. Then I took out the colon in front of v_vis_inn2_rfb_pts and put a colon in front of the next variable (v_vis_inn3_rfb_pts), like this (note where the colons are placed):

load_names_p.pr_load_names (...:v_vis_inn1_rfb_pts,v_vis_inn2_rfb_pts,:v_vis_inn3_rfb_pts...)

When I ran the file this time, I received this error msg:
SP2-0552: Bind variable "V_VIS_INN3_RFB_PTS" not declared.

This time it skipped over v_vis_inn2_rfb_pts (when it didn't have a colon preceding it) and gave me this error msg for the following variable v_vis_inn3_rfb_pts when I put a colon before it.

Everything looks spelled correctly and have the correct data types. I don't understand. Can you tell from this what is wrong?

It's possible there is some sort of bug when it has to handle a large number of variables. A workaround might be to assign the PL/SQL variables to the bind variables before calling the package. Also put a dbms_output statement against each bind variable. If it hasn't been declared properly you should get an error here as well.

Code:

---

declare
v_vis_c_fname emps.c_fname%TYPE;
v_vis_c_lname emps.c_lname%TYPE;
...
begin
dbms_output.put_line(:v_vis_c_fname);
v_vis_c_fname := :v_vis_c_fname;
dbms_output.put_line(:v_vis_c_lname);
v_vis_c_lname :=  :v_vis_c_lname;
..
load_names_p.pr_load_names (v_vis_c_fname, v_vis_c_lname, [more variables listed here]...);
end;

---

I will try that, but I think I will eventually run into the same problem of handling a large number of varibales. One thing that I began to do and could not figure out was to, instead of using one large procedure, use several smaller procedures within the same package body, but in order to do this I would need to reference the variables from these smaller procedures from another procedure (that is, declare global variables that can be referenced from anywhere in the package body). But, if I use 2 or more procedures in the same package body, I don't know how to make the variables in one procedure available in other procedures. I think this would solve the problems that I'm having. Can you help me with this?

This is an example of what I need to do:

CREATE OR REPLACE PACKAGE BODY load_employees_p AS
--load employees from DEPT1
PROCEDURE pr_load_dept1 (
v_emp1 OUT dept1.emps%TYPE,
v_emp2 OUT dept1.emps%TYPE)
IS
BEGIN
BEGIN
BEGIN
SELECT emp_name INTO v_emp1 FROM emps
WHERE emp_no=1 and dept=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
BEGIN
SELECT emp_name INTO v_emp2 FROM emps
WHERE emp_no=2 and dept=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
END pr_load_dept1;

--load employees from DEPT2
PROCEDURE pr_load_dept2 (
v_emp3 OUT dept2.emps%TYPE,
v_emp4 OUT dept2.emps%TYPE)
IS
BEGIN
BEGIN
BEGIN
SELECT emp_name INTO v_emp3 FROM emps
WHERE emp_no=1 and dept=2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEGIN
BEGIN
SELECT emp_name INTO v_emp4 FROM emps
WHERE emp_no=2 and dept=2;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END pr_load_dept2;

/*NOW THIS IS THE PART THAT I DON'T KNOW HOW TO DO. IN THE NEXT
PROCEDURE, I NEED TO BE ABLE TO ACCESS v_emp1, v_emp2, v_emp3, and
v_emp4 FROM THE PROCEDURES ABOVE.*/

--Update temp table
PROCEDURE pr_update_temp (
v_week_no OUT week_no.temp%TYPE)
IS
BEGIN
BEGIN
BEGIN
UPDATE temp SET emp_name=v_emp1 WHERE week_no=v_week_no and
emp_no=1; --v_emp1 NEEDS TO BE REFERENCED FROM ANOTHER PROCEDURE
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END pr_update_temp;
END load_employees_p;

How can I create this package body so that this last procedure can
use variables from the 1st 2 procedures? I think the way I have it
set up above is that all of the variables can only be accessed
within their own procedures. Thus, it will fail when pr_update_temp attempts to reference v_emp1, which is in pr_load_dept1. How can I make v_emp1... v_emp4 need to be global within the package body?

If you want to use global variables, then all you have to do is declare them at the top of the package body. You can then have another procedure which sets them.

Code:

---

create or replace pacakge body as
g_variable1  varchar2(50);
g_variable2 varchar2(10);
procedure set_vars (p_var1 in varchar2, p_var2 in varchar2) is
begin
g_variable1 := p_var1;
g_variable2 := p_var2;
end;
...
end;

---

I modified my code by taking just one variable in a procedure and making it global as in your example. I put the global variable in the declaration section at the top of both the specification and body and they compiled fine.

But when I attempted to set the global variable in the procedure like you did here:

begin
g_variable1 := p_var1;

and then re-compiled the spb file, I got the following error msg:

PLS-00371: at most one declaration for 'G_VARIABLE1' is permitted in the declaration section.

Since I've declared this global variable only once in the specification and once in the package body, I do not know what this error msg is referring to.

No - you must either declare in the specification or the body, but not both. Package spec variables are different to package body variables. Package spec variables can be addressed by external procedures e.g.

begin
my_package.g_my_variable := 'x';
end;

whereas package body variables can only be referenced within the package itself.

Possibly (wild guess) you are running into line length limitations within SQL*Plus. As a workaround you could try breaking the line into shorter ones, e.g:

EXEC blah(:blah, :blah -
, blah, :blah -
, blah :blah );

or replacing the SQL*Plus "EXECUTE" shortcut with the full PL/SQL "BEGIN ... END;" block syntax. Or of course reducing the number of parameters. Do you really need 168 of them?

Making procedure parameters into global variables sounds like a bit of a hack to me.

OK, I did that and got the global variables working so now I think can refer to them from any procedure. Thanks for your help on that and now I'll be able to implement it.

I'm still having a problem with the calling procedure (see below). I have 5 procedure calls in this file. The first 3 work fine with no problems. On the last 2 calls, I'm getting an SP2-0552 "Bind variable "V_VIS_INN1_FAN_PTS not declared" on the call for procedure pr_load_hitting_stats and SP2-0552 "V_VIS_SP_WHIP9_ERA not declared" on the call for procedure pr_load_pitching_stats. When I remove the colons from the bind variables, I get PLS-00201 "Identifier V_VIS_INN1_FAN_PTS not declared" for procedure pr_load_hitting_stats and PLS-00201 "Identifier V_VIS_SP_WHIP9_ERA not declared" for procedure pr_load_pitching_stats. I have all of the variables declared the same way in these last 2 procedures as in the first 3 procedures that are called just prior, so I can't figure out why I'm getting these errors.

At this point, in the specification I have all 5 procedures and their variables (parameters) declared, and in the package body the 5 procedures and their variables (parameters) declared, and have some statements that select values from database tables into the defined variables. All of the variables are declared as OUT. All of the data types are correct.

I don't have the space to paste everything, so I'll post a specification, package body, and the calling procedure of pr_load_hitting_stats.

This is a sample from the specification:

CREATE OR REPLACE PACKAGE play_games_p AS
.
.
.
PROCEDURE pr_load_hitting_stats (
v_vis_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE);

Here is a sample from the package body:

CREATE OR REPLACE PACKAGE BODY play_games_p AS
g_vis_inn1_fname VARCHAR2(10);
g_vis_inn1_lname VARCHAR2(25);
.
.
.
PROCEDURE pr_load_hitting_stats (
v_vis_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_vis_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn1_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn2_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn3_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn4_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn5_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn6_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn7_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn8_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE,
v_home_inn9_fan_pts OUT hitting_stats_weekly.fan_pts%TYPE)
IS BEGIN
BEGIN
BEGIN
SELECT fan_pts INTO v_vis_inn1_fan_pts FROM hitting_stats_weekly
WHERE week_no='1' AND fname=g_vis_inn1_fname AND lname=g_vis_inn1_lname;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
.
.
.
END pr_load_hitting_stats;
END play_games_p;

NOTE: g_vis_inn1_fname and g_vis_inn1_lname obtained their values from an earlier procedure in this package.

And here is the entire file that calls the procedures (the 1st 3 procedures work with no problem; the last 2 fail with the SP2-0552 Bind variable not declared errors:

SET serveroutput on size 1000000
SPOOL CALL_PROCEDURES.LOG
@cre_msg.sql
.
.
.
PROMPT CALL play_games_p.pr_load_hitting_stats
VAR v_vis_inn1_fan_pts NUMBER(3)
VAR v_vis_inn2_fan_pts NUMBER(3)
VAR v_vis_inn3_fan_pts NUMBER(3)
VAR v_vis_inn4_fan_pts NUMBER(3)
VAR v_vis_inn5_fan_pts NUMBER(3)
VAR v_vis_inn6_fan_pts NUMBER(3)
VAR v_vis_inn7_fan_pts NUMBER(3)
VAR v_vis_inn8_fan_pts NUMBER(3)
VAR v_vis_inn9_fan_pts NUMBER(3)
VAR v_home_inn1_fan_pts NUMBER(3)
VAR v_home_inn2_fan_pts NUMBER(3)
VAR v_home_inn3_fan_pts NUMBER(3)
VAR v_home_inn4_fan_pts NUMBER(3)
VAR v_home_inn5_fan_pts NUMBER(3)
VAR v_home_inn6_fan_pts NUMBER(3)
VAR v_home_inn7_fan_pts NUMBER(3)
VAR v_home_inn8_fan_pts NUMBER(3)
VAR v_home_inn9_fan_pts NUMBER(3)

EXEC play_games_p.pr_load_hitting_stats(:v_vis_inn1_fan_pts,:v_vis_inn2_fan_pts,:v_vis_inn3_fan_pts,:v_vi s_inn4_fan_pts,:v_vis_inn5_fan_pts,:v_vis_inn6_fan_pts,:v_vis_inn7_fan_pts,:v_vis_inn8_fan_pts,:v_vi s_inn9_fan_pts,:v_home_inn1_fan_pts,:v_home_inn2_fan_pts,:v_home_inn3_fan_pts,:v_home_inn4_fan_pts,: v_home_inn5_fan_pts,:v_home_inn6_fan_pts,:v_home_inn7_fan_pts,:v_home_inn8_fan_pts,:v_home_inn9_fan_ pts)
.
.
.
PROMPT END CALL PROCEDURES SCRIPT
SET serveroutput off
SHOW ERRORS
SPOOL OFF

Possibly (wild guess) you are running into line length limitations within SQL*Plus.

Thanks for your comments. Yes, I found this to be the problem when I tried to combine everything into one procedure, so I ended breaking the one large procedure into 5 shorter ones which resulted in breaking the calls into 5 shorters ones.

...or replacing the SQL*Plus "EXECUTE" shortcut with the full PL/SQL "BEGIN ... END;" block syntax. Or of course reducing the number of parameters.

I'm not sure what you mean here. Can you look at my code in the previous msg and give me an example? I'm new at this.

Do you really need 168 of them?

Yes.

Making procedure parameters into global variables sounds like a bit of a hack to me.

I'm not sure how else to reference a variable from one procedure that was declared and populated in another procedure within in the same package.

The only thing that I've noticed that could caise the last 2 procedures to fail is that the variables in the 2 procedures that do not work are numeric. The variables in the first 3 procedures that work are either VARCHAR2 or CHAR. The variables in the last 2 procedures that are giving the error messages are NUMBER(3) and DECIMAL(4,2). When I call these final 2 procedures, I get an SP2-0552 error. Is there something different that has to be done when calling procedures that have numeric variables?

I got one of the procedures that I was receiving an SP2-0552 error to work. What I thought was the data types were being somehow mismatched, so I replaced "hitting_stats_weekly.fan_pts%TYPE" with "NUMBER" in both the spec and package body. I tried doing the same for the last procedure that is not working due to this error, but the same solution did not work for it.

So using "table_name.data_type%TYPE" has worked for character data types, but did not work for numerical data types. No wonder I've heard so many programmers say they hate PL/SQL.

The one procedure that was working last night is not working this morning, even though I did not make any changes to that particular procedure. I added another procedure, and this new procedure is also giving the SP2-0552 error msg. The first 3 procedures are still working. Makes no sense.

Quote:

---

Originally Posted by alanv25

...or replacing the SQL*Plus "EXECUTE" shortcut with the full PL/SQL "BEGIN ... END;" block syntax.

I'm not sure what you mean here. Can you look at my code in the previous msg and give me an example?

---

SQL*Plus provides the EXECUTE command as a convenient shortcut so you can use

EXEC blah

in place of the full PL/SQL

BEGIN
blah;
END;
/

A restriction of SQL*Plus commands like EXECUTE is that they must be entered on one line, or broken using the continuation character ("-" by default). Therefore

EXEC blah(blah, blah, blah)

is the same as

EXEC blah -
( blah -
, blah -
, blah )

which is also the same as

BEGIN
blah
( blah
, blah
, blah );
END;
/

Quote:

---

Originally Posted by alanv25

Making procedure parameters into global variables sounds like a bit of a hack to me.

I'm not sure how else to reference a variable from one procedure that was declared and populated in another procedure within in the same package.

---

Sorry, but I'm afraid that also sounds a bit of a hack. Why can't you pass into each procedure the exact values it needs?

Regarding the procedure calls / bind variables / data types not working as expected, can you narrow it down to a repeatable test case I can try out myself?

William, thanks for your help. All I've ever been exposed to was calling a procedure from SQL*Plus using EXEC. I was trying to find out how to use the BEGIN... END format and will try to implement it now that you've explained it.

As for the SP2-0552 error, I figured out what the problem was late last night. I asked on the DBA forum of this site how I could query the variables and data types of a procedure and found out that I could use a simple
DESC package_name
When I did this, it showed that all of my numeric variables were listed as NUMBER (rather than NUMBER(x)). So I had to change all of the variable declarations in the file that called the procedures from NUMBER(3) to NUMBER. I tried that before, but something else must have been causing an error, preventing it from working. This time when I tried it, it worked.

Thanks again.