[intro music]
Hi, I'm Steven Feuerstein and I write Practically Perfect PL/SQL.
[music]
So, let's take a look at one of the most
frustrating areas
that PL/SQL developers encounter on compile time and that's
PLS-00306 wrong number or types of arguments
in a call to subprogram
So, as the Oracle error messages document says, 'this error occurs when the named
subprogram call cannot be matched to any declaration for that subprogram name.
So, the subprogram name might be misspelled,
a parameter might have the wrong datatype, it might be the wrong number, which is pretty much what it says
in the error message. So, you might say, well, what's the big deal.
Well, the big deal is that unfortunately Oracle doesn't
tell us which argument name might be wrong or which one is missing or
there are too many or there are too few. So, you often have to do little detective work
to sort out what the problem could be.
For simple programs, they can be simple. For more complicated programs, a bit more complicated.
Let's take a look at some examples. So, let's start with the package called Sales Package.
It has three different subprograms  all
called calc_total.
So, its overloaded. One of them has a parameter called zone_in type String.
The second procedure has a parameter called
region_in, also a String. And the third subprogram is a function that has
the same parameter zone_in. Single String returning a Number.
The implementation is rather silly, just displays a message.
or returns Zero.
So, the zone_in version displays zone and reg_in version displays region.
And these packages as you can see here both compiled without any error whatsoever. Oracle said they are fine.
Let's try using them. I wrote a program, sure would be nice to use it.
So, as you can see on my call to the Sales
package calc_total program, I am
passing it a value of TRUE. Lets see what happens when I run this code.
And the answer is,
Compilation error, there it is, 306.
Wrong number or types of arguments in call to calc_total.
Why did that happen?
Well, let's go back to the definition of the 
package.
I have two different procedure overloadings
and I called a procedure. So, first of all, Oracle says, well, I need to look at the two
procedure definitions, not the function.
And it says, I have got a String for zone_in and I have got a String for region_in.
And, the question is, "is this a String?"
No, its not a String, that's a Boolean value.
Can it be converted to a String? No, you can't convert Booleans to
Strings. So, Oracle says that's the wrong
type for this argument.
Now, if I put in 123, that's right there, right?
Passing 123. That's a String. So, that should work, right?
Run my code again.
Now, in this case, I have got a slightly different error which I will explore
in detail in a separate episode which is 307.
Too many declarations of calc_total match this call.
So, the problem wasn't that with my data type or the number of arguments I'm passing.
I have only got one I can pass in for any of these. And they are Strings. So, the problem is,
Oracle couldn't figure out which one. Like, I say, we will come back to that.
But, definitely passing TRUE dint work. And, if I pass more than one parameter,
so, passed two Dates.
Run my code.
Wrong number or types of arguments in call to calc_total. So, as I mentioned before
for simple occurrences, what's the big
deal. I look at my code, it says,
wrong number types or types .. I have got one, two.
Go back to my original version of my code. The definition
and I look and I see one.. no two! I can't have two arguments
So, I'm going to go back and say it shouldn't be two, it should just be one.
Run my code and aim maybe same but it doesn't accept Date, it accepts Strings.
You are going to get an error.
Ah, well, in this case, still so many declarations
But let's fix that for a moment
Just to show you that in fact I can call this program with a Date.
No problem, because of course, a Date can be converted to a String.
So while we might look at this and say that looks like the wrong type.
For Oracle, it is not the wrong type because,
Oracle makes such an effort to help us get our job done including implicit conversions of data types.
Even if it is not always such a great idea to rely on Oracle to do that for us.
OK
Now, another problem we might run into.
relates to Named notation.
So, what I wrote right here and I kind of skipped over the explanation of it, is
I explicitly associated this parameter.. this parameter
zone_in with this argument value sysdate. So the equal
greater than symbol is the association-a
symbol
and it is called Named Notation. I am naming which parameter
goes with which argument value. And that worked. Here's another version that doesn't work so well.
So, passing ABC is associated with this zone.
Because, you know, at a glance, I have got zone_in String.
Good to go. Run my code.
Oh no! PLS 306
wrong number or types of arguments. So
what's going on.
Well again, because it's a simple program,  it's pretty easy to see
that my parameter name is not correct. It needs to be zone_in
Then I run it.
No problem.
Now, when you have a parameter list of 15 to 20 parameters, it's often hard to see
what the problem might be.
For example, what I often run into, its because my standard approach, my
naming convention for parameters is
underscore in or underscore out and so on. Sometimes,
in a long list of parameters I will leave off the underscore in just one of them and I realize,
of course Oracle says, that's fine, you don't
have to have that
suffix on your parameter name.
So, it compiles, right? But then when I try to use it,
and I use my convention, it says, Sorry, that doesn't work. And it takes me
a while to realize
to actually question the assumption, did I define my procedure correctly?
So, sometimes it can be very
frustrating to track down the cause of
306. Probably, the most important thing is to not assume anything. Take a step back and
ask, did I define the parameter list as I'm using it here.
Are these values compatible with the
datatypes
of the parameters themselves.
And, this is an interesting example too. For suppose, I have got a cursor FOR loop,
So, for every employee in this SELECT FROM employees table,
I'm going to display the employee Last Name.
So, that.. Let us see if that works.
Run my code.
Anonymous block completed, if we look at the output.
And I try it on.
We see the code.
There are all my_employees. But what sometimes happens is something more like this
Display my_employee.. Which employee..
Just display.. Seems no reason
Run my code.
Wrong number or types of arguments in call to 'PUT_LINE'
Well, 'PUT_LINE' only accepts Strings or anything that can be implicitly converted to a String.
And there is no way Oracle can convert a Record to a String.
So again in simple code, it just stand out pretty clearly that you missed something here.
But, in more complicated code, especially depending on the way you name things,
it could be hard to get focused on what could be causing that problem.
And finally,
just to give you an interesting glimpse at how the compiler works.
I'm gonna do roughly the same code but I'm going to say Name equals.
And concatenate it to employee.
I run my code.
And now I get an interesting message,
wrong number or types of arguments in call to the concatenation operator.
That's kind of strange looking, is it.
We think about the concatenation
operators being a built-in part of the language
Its not a function that you are passing parameters to.
But in fact, it does function.. work in the same way. So, whats happening now is that
Oracle is complaining about the fact that
I'm trying to concatenate
a Record to a String.
Just doesn't make any sense.
When... wrote it, it seemed OK.
And the fix, of course, just specify the individual field.
That will give you the data you want. And that will show you what you want.
There are the names. Some more down here. Way down there.
All the new names.
So, probably the most important message
about the 306 error,
is to really believe in, wrong number or
types of arguments
in call to that program. So, the fist thing you do is check
the number. Do I have the correct number of parameters that I'm being passing, that I'm passing through
If I have too many, that is usually
pretty clear. I have got 4 parameters,
I have got 5 values. Doesn't work.
The trickier part, is when you have got small number of parameters that are being passed
into the procedure, in the full list,
because you have default values and
you want use the defaults. And then you might be using named notation.
Then it can get kind of tricky to figure out what's missing.
whether the the.. where things are co-relating properly.
In terms of wrong types. That can get tricky because of the implicit conversions.
But if you take Oracle at its word, that should looking at a problem with a number or the data type,
even for longer complex lists as long
as you focus
on the actual text that is there. Don't make assumptions. Don't say,
I know how I name things. Actually look at what you wrote.
You can usually sort out the problem relatively shorter. So hopefully these tips will help you
with your PLS 306
And happy PL/SQL Coding.
[exit music]
