CODEWITHSUNDEEP
oracle-databaseplsql
JDro04
JDro04

Reputation: 670

Overloading oracle procedure with DATE and VARCHAR2

I have the following code that doesn't work. It compiles, but when called with sysdate as the parm_value parameter it throws PLS-00307: too many declarations of 'P_UPSERT_SDE_DATA' match this call If I comment out the varchar2 entry, the overload works as expected with just date and number datatypes.

What is the best way to go about what I'm trying to do, which is accept parameters differing only in parm_values datatype(specifically date and varchar2)?

  PROCEDURE P_Upsert_SDE_Data(parm_table_name   GORSDAV.GORSDAV_TABLE_NAME%TYPE,
                              parm_attr_name      GORSDAV.GORSDAV_ATTR_NAME%TYPE,
                              parm_key              GORSDAV.GORSDAV_PK_PARENTTAB%TYPE,
                              parm_user_id        GORSDAV.GORSDAV_USER_ID%TYPE,
                              parm_value            VARCHAR2);
--                            
  PROCEDURE P_Upsert_SDE_Data(parm_table_name   GORSDAV.GORSDAV_TABLE_NAME%TYPE,
                              parm_attr_name      GORSDAV.GORSDAV_ATTR_NAME%TYPE,
                              parm_key              GORSDAV.GORSDAV_PK_PARENTTAB%TYPE,
                              parm_user_id        GORSDAV.GORSDAV_USER_ID%TYPE,
                              parm_value            NUMBER);

  PROCEDURE P_Upsert_SDE_Data(parm_table_name   GORSDAV.GORSDAV_TABLE_NAME%TYPE,
                              parm_attr_name      GORSDAV.GORSDAV_ATTR_NAME%TYPE,
                              parm_key              GORSDAV.GORSDAV_PK_PARENTTAB%TYPE,
                              parm_user_id        GORSDAV.GORSDAV_USER_ID%TYPE,
                              parm_value            DATE);

Upvotes: 3

Views: 636

Answers (2)

Alex Poole
Alex Poole

Reputation: 191455

You can see this if one of the others arguments you pass is being implicitly converted; from the call you posted I suspect var_FRB‌​GRNT_CODE is a different type and is being converted; e.g. that variable is a number and GORSDAV.GORSDAV_PK_PARENTTAB is a string.

From the documentation:

When trying to determine which subprogram was invoked, if the PL/SQL compiler implicitly converts one parameter to a matching type, then the compiler looks for other parameters that it can implicitly convert to matching types. If there is more than one match, then compile-time error PLS-00307 occurs, as in Example 8-34.

Implicit conversion of one of the other arguments makes it look for potential conversions of the others; it's only then that it sees the date and varchar2 versions, which can be implicitly converted to each other. If all of the arguments are of the same type as the table columns used for the formal parameter declarations then it won't look for implicit conversions, and won't be confused by the date/varchar2 versions.

Upvotes: 3

DCookie
DCookie

Reputation: 43533

Whenever I get this error, it seems to be that there's a mismatch between the spec declaration and the body declaration somewhere. Make sure each of your spec declarations match your body declarations exactly.

Upvotes: 0

Related Questions