Friday, March 09, 2007

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!

22 comments:

GEQ said...

Bless you! I spent a day trying to find out what was wrong with my code.
I never imagined that different *names* of variables between the spec and the body would cause this error.
Thanks again.

Anonymous said...

Thank you very much! I spent several hours screaming "I have declared it!" Fortunately I found your blog before I pulled out any hair.

-Bill

Doug said...

Thank you for posting this information! I encountered PLS-00230 today and found your blog, and after getting my head around the real meaning of the message, I discovered ANOTHER way that the error can be raised (as a result of my coding). When a procedure or function references other function(s) in a package, the spec and body of the referenced function(s) must appear prior to the procedure or function that contains the referencing statements. For example, if you have package spec function A; function B; and function A refers to function B, you'll get PLS-00230 for function A. If you reorder the spec and body so that it lists as function B; function A;, you get successful compilation (if you have no other errors). Can you verify and add this info to your list of causes? Thanks.

Doug said...

Correction to previous post - I did get PLS-00323, not PLS-00230.

Byte64 said...

Doug,
concerning the second problem you report, that's the way it works unless you adopt the so-called forward declaration method.
There is an excellent explanation of this method in Eddie Awad's blog.

Thank you,
Flavio

cutecolt said...

Thanks for your post. I didn't expect oracle to behave in such a way. Anyhow, you saved me from banging my head against the wall. To put it simply, just copy ur parameter argument from package specification to package body. This will eliminate all fuss..Happy coding..

sabyasm said...

Thanks for this :)
I was literally scratching my head for this!!!

Anonymous said...

Thanks a lot of the information. Still getting used to the huge number of differences between Oracle and SQL Server. Fortunately, I have been able to keep the "scream therapy" internal so far.

George

Coolbuddy said...

Thank You very much for the usful blog. It really saved my time!

coolbuddy said...

Thank You very much for the usful blog. It really saved my time!

Anonymous said...

Thanks
Now i know that there is a differnce between P1_varible and PI_varible.
:-)

Anonymous said...

Briliant!!

10 coffies saved!
Thank You man!

FL said...

Bless you child. The different parameter name got me. Thanks for your help. I was going nuts.

Anonymous said...

Thank you so much. I spent an hour wondering what was wrong when I have already defined the procedure

Anonymous said...

With your help, it was simple. Thanks! I'd been looking at this crazy thing for about an hour. Fixed is less than a minute with your explanation.

Anonymous said...

Thanks. I just could not see it - never did. A copy and paste fixed it.

Anonymous said...

It really helped me.. thanks for the blog :)

@GEQ
thanks for your mentiong that difference in the *names* caused the error. I faced the same and now it is resolved

Anonymous said...

Thanks a lot for the post.And u have written the exact thought that is going on in mind after getting the error msg...:):). Again thanks a lot..:)

Recombinant Socks said...

Thanks for this. I guess practically every Oracle coder has stubbed their toes on this one. How much lost coding time has this caused and how much value has the strict functionality created? Methinks that Oracle could improve their system by allowing different names and allowing forward declaration automatically!

Anonymous said...

For the love of God thank you!!! I was testing using IN OUT instead of just IN, and forgot to update the package body where I defined the procedure. Caused this annoying error, and I spotted it after an hour of hair pulling thanks to your post.

ishek said...

Thanks you!
2. the input/output (in/out) declaration is different
You have been Google plused :)

Anonymous said...

Thanks !

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles