I have a package with an overloaded function, wich accepts one parameter (INTEGER / VARCHAR2)
and returns a booblean indicating if exists a register with the correspondient column having
the same value as the parameter.
In tests, when the input parameter is null, it seems to don't distinguise wich version of EXISTE may execute.
If I convert the parameter to number or char, it does.
Someone knows another more elegant way of doing it instead of casting?
The summary description of the table consulted by the function is:
<b>CREATE TABLE TGEOMUNICIPIOS
(
ID NUMBER(*,0),
CODINEMUNICIPIO VARCHAR2(11 BYTE)
);</b>
Insert only one record to prove the issue:
<b> INSERT INTO TGEOMUNICIPIOS VALUES ( 6015, '06015000000' );
COMMIT;</b>
The package specification and body are:
<b>create or replace PACKAGE PAQ_TGEOMUNICIPIOS
IS
...
FUNCTION EXISTE
(
pe_idmunicipio IN TGEOMUNICIPIOS.ID%TYPE
)
RETURN BOOLEAN;
/*
Devuelve un booleano indicando si existe un municipio con el identificador
proporcionado.
*/
FUNCTION EXISTE (
pe_inemunicipio IN TGEOMUNICIPIOS.CODINEMUNICIPIO%TYPE
)
RETURN BOOLEAN;
/*
Devuelve un booleano indicando si existe un municipio dado su código ine.
*/
...
END PAQ_TGEOMUNICIPIOS;
create or replace PACKAGE BODY PAQ_TGEOMUNICIPIOS
IS
...
FUNCTION EXISTE
(
pe_idmunicipio IN TGEOMUNICIPIOS.ID%TYPE
)
RETURN BOOLEAN
IS
v_existe INTEGER;
BEGIN
SELECT ID
INTO v_existe
FROM TGEOMUNICIPIOS
WHERE ID = pe_idmunicipio;
RETURN TRUE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN FALSE;
END EXISTE;
FUNCTION EXISTE (
pe_inemunicipio IN TGEOMUNICIPIOS.CODINEMUNICIPIO%TYPE
)
RETURN BOOLEAN
IS
v_existe INTEGER;
BEGIN
SELECT ID
INTO v_existe
FROM TGEOMUNICIPIOS
WHERE CODINEMUNICIPIO LIKE pe_inemunicipio;
RETURN TRUE;
EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;
END EXISTE;
...
END PAQ_TGEOMUNICIPIOS; </b>
The test code:
<b>DECLARE
BEGIN
--next line doesn't distinguish which version to run
dbms_output.put_line ( paq_conversiones.f_boolean_a_cadena ( paq_tgeomunicipios.existe ( null ) ) );
--next line forces to run the first version
dbms_output.put_line ( paq_conversiones.f_boolean_a_cadena ( paq_tgeomunicipios.existe ( TO_NUMBER( null ) ) ) );
--next line forces to run the second version
dbms_output.put_line ( paq_conversiones.f_boolean_a_cadena ( paq_tgeomunicipios.existe ( TO_CHAR( null ) ) ) );
END; </b>
It returns:
<b>ORA-06550: línea 5, columna 65:
PLS-00307: too many declarations of 'EXISTE' match this call
ORA-06550: línea 5, columna 4:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:</b>
Here's a cut down version of your example
SQL> create or replace
2 package body pkg is
3 procedure P(x int) is begin null; end;
4 procedure P(x varchar2) is begin null; end;
5 end;
6 /
Package body created.
SQL>
SQL> exec pkg.p(1);
PL/SQL procedure successfully completed.
SQL> exec pkg.p('1');
PL/SQL procedure successfully completed.
SQL> exec pkg.p(null);
BEGIN pkg.p(null); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00307: too many declarations of 'P' match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
But the reality is - this is rarely an issue. Because its very rare that we'll pass the explit term 'null' to a package. They *receive* something from somewhere else. And as long as we *know* the datatype, it will work fine. For example
SQL> declare
2 var int;
3 begin
4 pkg.p(var);
5 end;
6 /
PL/SQL procedure successfully completed.
So whilst it looks like an issue - I think you'll rarely be hit by it. After all, how often to you intend to explicitly call your package with a literal not a variable as the parameter ?