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

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.