Thanks for the question, Simeon.

Asked: December 20, 2010 - 12:36 pm UTC

Last updated: June 12, 2012 - 4:38 am UTC

Version: 3.0.02

Viewed 10K+ times! This question is

You Asked

Hi, Tom.

First of all I enjoyed meeting you at Oracle Develop in September.

This should be a simple question. I am trying to overload a function with the following signatures:

function WEEKS_IN_FY (eff_date in date) RETURN integer;
function WEEKS_IN_FY (FY in integer) RETURN integer;

However, I get the message ORA-06553: PLS-307: too many declarations of 'WEEKS_IN_FY' match this call.

As far as I can tell the signatures are different. I cannot figure out why PL-SQL doesn't recognize the different signatures.

Thanks for your help.

and Tom said...

No example :(

(why doesn't anyone give examples???? I ask for them, you read a page that you had to check a box on that said you had read that I ask for them). This is at least the fifth question I've received today that *had no example to demonstrate the issue whatsoever*)

I could just post an example that says "it works - see", but I'll guess as to the problem here.

You have a function that takes a date. You have a function that takes an integer. You are calling said function with neither a date nor an integer, but a string. A string could be implicitly converted into a date OR a number. Hence - it is ambiguous.

The solution? Don't send strings - send DATES or INTEGERS - never use/rely on implicit conversions

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          function f( x in date ) return integer;
  4          function f( x in integer ) return integer;
  5  end;
  6  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3          function f( x in date ) return integer
  4          as
  5          begin
  6                  return 1;
  7          end;
  8  
  9          function f( x in integer ) return integer
 10          as
 11          begin
 12                  return 2;
 13          end;
 14  
 15  end;
 16  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_output.put_line( my_pkg.f( 1 ) );
2

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( my_pkg.f( to_date('01-jan-2010','dd-mon-yyyy') ) );
1

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_output.put_line( my_pkg.f( '1' ) );
BEGIN dbms_output.put_line( my_pkg.f( '1' ) ); END;

                            *
ERROR at line 1:
ORA-06550: line 1, column 29:
PLS-00307: too many declarations of 'F' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


ops$tkyte%ORA11GR2> exec dbms_output.put_line( my_pkg.f( '10-jan-2010' ) );
BEGIN dbms_output.put_line( my_pkg.f( '10-jan-2010' ) ); END;

                            *
ERROR at line 1:
ORA-06550: line 1, column 29:
PLS-00307: too many declarations of 'F' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Too Nice

Bill C, December 20, 2010 - 2:18 pm UTC

You are way too nice. It must be Christmas.

I hope he enjoys the gift.

Thanks

Simeon Brett, December 20, 2010 - 3:41 pm UTC

Thank you, Tom. This really helped. In retrospect, the answer should have been obvious, but it wasn't.

Sorry about the lack of examples. This is my first question to you and I thought by giving the signatures, it was all you were looking for,

Thank You!

Mary Couette, February 17, 2011 - 12:42 pm UTC

I had a similar issue. I was passing a string forced to a number because of a computation on the value from a sub-query to an overloaded function expecting a varchar. It works if I force it to a char or a number, but if I leave it at whatever ambiguous type the sub-query leaves it, it fails.

Overloading useful?

A reader, June 12, 2012 - 1:47 am UTC

Hi Tom,

I find overloading of procedures etc to be a pain the ...
trying to maintain someone elses code is annoying with overloading.

What is the benefit to overloading?
and have you done any tests on if it is efficient?

Lets say we a procedure that is overloaded depending
on if a string, a date or a number is passed

It seems to me the optimzier needs to go searching for the matching function or stored prcedure

Would'nt it be easier to have my_sp_string, my_sp_date, my_sp number? that way exec my_sp_string or exec my_sp_date just gets executed rather than lets look at the parameters and go looking for which overloaded version of my_sp() matches

Regards
Doug

June 12, 2012 - 4:38 am UTC

What is the benefit to overloading?

ease of use.

Look at the DBMS_SQL package - overloaded for dates, numbers, strings, clobs, etc. Rather than have "dbms_sql.bind_a_number, dbms_sql.bind_a_string, ...." you just have "dbms_sql.bind_variable" - one routine that does the same thing regardless of type.

And that is no harder to maintain then having a bind_a_number, bind_a_string, etc.

The looking bit your are talking about happens at compile time, there isn't a run time overhead involved.

Some people swear by overloading.
Some people swear at overloading.

fully 1/3 of the Oracle supplied packages make use of it. I've made use of it. I view it as a positive thing in general. If you code in a modular fashion, there shouldn't be any maintenance issues with it.

Fair enough

A reader, June 12, 2012 - 5:12 am UTC

Point taken about dbms_sql

>If you code in a modular fashion, there shouldn't be any >maintenance issues with it.

This statement highlights its probably more how I see people using overloading more than overloading itself
Thankyou

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Too Nice

Thanks

Thank You!

Overloading useful?

Fair enough

More to Explore

PL/SQL demos

PL/SQL docs

Overloading a function

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Too Nice

Thanks

Thank You!

Overloading useful?

Fair enough

More to Explore

PL/SQL demos

PL/SQL docs