PLS-00323 and PLS-00593

Raise her/his hand anyone who didn't get confused the first time she/he got this error.

In my humble opinion this one of the most deceiving error messages ever conceived.

I mean, when all is said and done, it does actually make sense, but it's just so puzzling for any beginner to get a grasp on what oracle is complaining about!

Let's take for instance the simple test_pkg source code from the previous posting and see what happens if i artificially alter it in order to cause this error condition.

create or replace Package Test_Pkg as
Procedure Test (
par_a  in varchar2,
par_b  in number);
End Test_Pkg;
/

create or replace Package Body Test_Pkg as
Procedure Test (
par_a  in varchar2,
par_b  in varchar2)
is
begin
null;
end;
End Test_Pkg;
/

PLS-00323: subprogram or cursor 'TEST' is declared
in a package specification and must be defined in
the package body

As you see, par_b is declared as number in the package specification and as varchar2 in the package body.
Oracle doesn't alert you about this fact, but complains about the alleged absence of subprogram Test.

Normally the first emotional reaction of a developer is to take an error message verbatim, so one goes out and checks for the specification and starts wondering why on earth Oracle says that procedure Test isn't there.
Sure it is there, i can see it with my very eyes!

The example above is trivial, there is just one procedure in a package, but when you have dozens of procedures, functions and so on, it's not so trivial to spot a tiny difference.

So, why is Oracle complaining about the absence of a subprogram that is indeed existing?
I don't know the internal workings exactly, but i guess that procedure overloading has something to do with it.

• Beginning short informal explanation of overloading, skip it if you know what i'am talking about.
• 
  
• As you know, Oracle let's you define overloaded procedures or functions, that means you can have a procedure accepting either a string or a number or whatever object type you like best using the same parameter name.
  
• What's for?
• Well, for instance you can have a function accepting a date parameter either as a date object or as its formatted string equivalent, plus the date format model as an optional parameter, just to make programmers' life easier.
  
• In order to do such a thing, you need to define two distinct functions in the package specification and in the package body, each one handling one of the two distinct cases, that is one for the date parameter of type date and one for the date parameter as string, plus its accompanying format model parameter.
  
• You can even end up having a different number of parameters. Cool!
  
• Still not clear with it?
• Oracle built-in packages are plenty of such overloaded procedures and functions, look at dbms_lob package, there are several examples in there.
  

• describe dbms_lob

• 
  

So, with the overloading feature in mind, you can now figure out why oracle is saying that it cannot find that subprogram. Oracle prefers to think of us as very forward looking developers and it's saying: hey look out, you forgot to declare this (overloaded) version of the procedure in the specification, rather than accepting the blunt reality: we are poor mortals.
We changed or forgot to add some text either in the body or in the specification, making them look different.

But it's so noble of the compiler to hold us in such high esteem!

In the end this type of error can be caused by a variety of situations:

1. the parameter type is different
2. the input/output (in/out) declaration is different
3. the name of the parameter is different (watch out for typos or renamed parameters!)
4. the position of the parameter is different
5. the number of parameters is different
6. the procedure or function name is different
7. function's return type is different
   
8. and problably even others that do not come to my mind in this moment.
   

So, now that we have spotted where the catch is, let's do another experiment:

create or replace Package Body Test_Pkg as
Procedure Test (
par_a  in varchar2,
par_b  in number default null)
is
begin
null;
end;
End Test_Pkg;
/

PLS-00593: default value of parameter "PAR_B"
in body must match that of spec

Now oracle is adjusting the focus.

Since the specification and the body are almost equal, except for the default parameter value, it's throws a much more precise message and his optimistic attitude towards us is gone.

Happy overloading!