joekeri
asked on
OVERLOADING a FUNCTION
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?
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?
ASKER
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_numbe r%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_numbe r%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;
/
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_numbe
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_numbe
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;
/
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_f sa_card('T EST')
FROM DUAL;
SELECT PKG_is_it_fsa_card.is_it_f
FROM DUAL;
ASKER
I ran SELECT PKG_is_it_fsa_card.is_it_f sa_card('T EST')
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I took your sugesstion and abandoned the OVERLOADing function and had it just return a BOOLEAN.
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.
Open in new window