Solved

OVERLOADING a FUNCTION

Posted on 2007-11-26
6
547 Views
Last Modified: 2013-12-07
I am OVERLOADING a package.

I have a PACKAGE called VERIFY_CARD_TYPE .  It in are two functions, into which I pass a credit_card_number.

The FUNCTIONS are called IS_IT_FSA.  One returns a BOOLEAN value and the other returns a VARCHAR


What I need to know is; can I choose to test either the BOOELAN or VARCHAR returned values?  Or do I have to specify which of the FUNCTIONS I want to execute?
0
Comment
Question by:joekeri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20351041
I am not sure if functions returning diff. data-types, but accepting similar data-types can be overloaded successfully. For a function to be over-loaded, Oracle must be able to identify which signature to call at run-time for which it relies on the function definition indicated by the parameters accepted by the function, not by the return type. However, if more than 1 function with same signature, then Oracle cannot determine which one to call (see attached for demo).

In your case, it would be better to use a simple procedure with 2 OUT parameters - 1 for BOOLEAN and other for VARCHAR2 and use any 1 of these parameters depending on the logic.
SQL> create or replace package jak_pkg as
  2  
  3  function jak_fun return boolean;
  4  function jak_fun return varchar2;
  5  
  6  end jak_pkg;
  7  /
 
Package created.
 
SQL> create or replace package body jak_pkg as
  2  
  3  function jak_fun return boolean is
  4  begin
  5  return true;
  6  end;
  7  
  8  function jak_fun return varchar2 is
  9  begin
 10  return 'true';
 11  end;
 12  
 13  end jak_pkg;
 14  /
 
Package body created.
 
SQL> declare
  2  v boolean;
  3  begin
  4  v := jak_pkg.jak_fun;
  5  dbms_output.Put_line(v);
  6  end;
  7  /
v := jak_pkg.jak_fun;
             *
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00307: too many declarations of 'JAK_FUN' match this call
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
ORA-06550: line 5, column 1:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored
 
SQL> declare
  2  v varchar2(10);
  3  begin
  4  v := jak_pkg.jak_fun;
  5  dbms_output.Put_line(v);
  6  end;
  7  /
v := jak_pkg.jak_fun;
             *
ERROR at line 4:
ORA-06550: line 4, column 14:
PLS-00307: too many declarations of 'JAK_FUN' match this call
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
 
SQL> 

Open in new window

0
 

Author Comment

by:joekeri
ID: 20351076
Here is my SPEC and BODY. From what I can see, my example is similar in structure to yours.

CREATE OR REPLACE PACKAGE PKG_is_it_fsa_card AS
FUNCTION IS_IT_FSA_CARD (P_card_number_decrypted IN VARCHAR2)
         RETURN BOOLEAN deterministic;

FUNCTION IS_IT_FSA_CARD (P_card_number_decrypted IN VARCHAR2)
         RETURN VARCHAR2 deterministic;
END PKG_is_it_fsa_card;
~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE PACKAGE BODY PKG_is_it_fsa_card IS

-------------------------------------------------------------------------------
FUNCTION is_it_fsa_card (P_card_number_decrypted IN varchar2)
RETURN BOOLEAN IS

   l_starting_number       fsa_bin_ext.starting_number%type := null;
   

BEGIN
  SELECT FSA.starting_number
    INTO l_starting_number
    FROM FSA_BIN_EXT FSA
    WHERE P_card_number_decrypted BETWEEN FSA.starting_number and FSA.ending_number;
   

  RETURN (TRUE);
 
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        RETURN (FALSE);
   WHEN TOO_MANY_ROWS THEN
        RETURN (TRUE);
   WHEN OTHERS THEN
        RETURN (FALSE);
END is_it_fsa_card;
--------------------------------------------------------------------------------
FUNCTION is_it_fsa_card (P_card_number_decrypted IN varchar2)
RETURN varchar2 IS

   l_starting_number             fsa_bin_ext.starting_number%type := null;
   l_is_it_fsa_flag              varchar2(1)    := 'Y';
     

BEGIN
  SELECT FSA.starting_number
    INTO l_starting_number
    FROM FSA_BIN_EXT FSA
    WHERE P_card_number_decrypted BETWEEN FSA.starting_number and FSA.ending_number;
   

  RETURN (l_is_it_fsa_flag );
 
 
EXCEPTION
   WHEN NO_DATA_FOUND THEN
        l_is_it_fsa_flag   := 'N';
        RETURN (l_is_it_fsa_flag );
   WHEN TOO_MANY_ROWS THEN
        l_is_it_fsa_flag   := 'Y';
        RETURN (l_is_it_fsa_flag );
   WHEN OTHERS THEN
        l_is_it_fsa_flag   := 'N';
        RETURN (l_is_it_fsa_flag );
END is_it_fsa_card;
END PKG_is_it_fsa_card;
/
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20351126
Yes, your example is pretty much similar as mine. Unfortunately (as evident in my post too) Oracle does not throw any error when you compile this pkg. body as it does not compare the function signatures at compile-time. However, if you try to call this function from outside, you should be thrown an error. Just fire this and see what happens : -

SELECT PKG_is_it_fsa_card.is_it_fsa_card('TEST')
FROM DUAL;
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:joekeri
ID: 20351237
I ran SELECT PKG_is_it_fsa_card.is_it_fsa_card('TEST')
FROM DUAL;

and this is the error i got back

ORA-06553: PLS-307: too many declarations of 'IS_IT_FSA_CARD' match this call
0
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 50 total points
ID: 20351279
Correct. As i said, Oracle could not determine the right signature to call, so it threw an error.
Essentially, functions should be over-loaded only when you have to perform similar operations on a variety of data-types e.g. addition operation for a string would be diff. from the same for a date. However, in your case, since both of your functions process the same type of data i.e. VARCHAR2 (credit_card_no), you should use only a single function returning either a BOOLEAN or a VARCHAR2, based on your requirements.
0
 

Author Closing Comment

by:joekeri
ID: 31411000
I took your sugesstion and abandoned the OVERLOADing function and had it just return a BOOLEAN.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sum of columns in a row in oracle 3 62
Sorting a SQL script 5 42
Check for any ASM patches and install them. 1 25
Oracle Date add 9 36
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question