PLS-00307: Too Many Declarations of “X” Match This Call

Your Location is: Home > Sql

PLS-00307: Too Many Declarations of “X” Match This Call

From: Vanuatu View: 1254 Nyyen8

Question

I'm having a common issue it seems but other solutions haven't seemed to correct what I have going wrong. When making the package call to invoke my procedures it is saying the call is ambiguous. I am pulling from the same table and can verify that the selected variable types are different. So why is is failing when the input is clearly and number and a string?

Package Header

CREATE OR REPLACE PACKAGE shop_query_pkg IS
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.idshopper%TYPE,
        p_name_output OUT bb_shopper.idshopper%TYPE,        
        p_city_output OUT bb_shopper.city%TYPE,
        p_state_output OUT bb_shopper.state%TYPE,
        p_phone_output OUT bb_shopper.phone%TYPE,
        p_email_output OUT bb_shopper.email%TYPE); 
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.lastname%TYPE,
        p_name_output OUT bb_shopper.idshopper%TYPE,        
        p_city_output OUT bb_shopper.city%TYPE,
        p_state_output OUT bb_shopper.state%TYPE,
        p_phone_output OUT bb_shopper.phone%TYPE,
        p_email_output OUT bb_shopper.email%TYPE); 
END;
/
CREATE OR REPLACE PACKAGE BODY shop_query_pkg IS
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.idshopper%TYPE,
        p_name_output OUT bb_shopper.idshopper%TYPE,        
        p_city_output OUT bb_shopper.city%TYPE,
        p_state_output OUT bb_shopper.state%TYPE,
        p_phone_output OUT bb_shopper.phone%TYPE,
        p_email_output OUT bb_shopper.email%TYPE)
        IS
        BEGIN
            SELECT firstname || lastname AS "Name", city, state, phone, email
            INTO p_name_output, p_city_output, p_state_output, p_phone_output, p_email_output
            FROM bb_shopper
            WHERE idshopper = p_id_input;
    END;  
    
    PROCEDURE shop_lookup_pp
            (p_id_input IN bb_shopper.lastname%TYPE,
            p_name_output OUT bb_shopper.idshopper%TYPE,        
            p_city_output OUT bb_shopper.city%TYPE,
            p_state_output OUT bb_shopper.state%TYPE,
            p_phone_output OUT bb_shopper.phone%TYPE,
            p_email_output OUT bb_shopper.email%TYPE) 
        IS
        BEGIN
            SELECT firstname || lastname AS "Name", city, state, phone, email
            INTO p_name_output, p_city_output, p_state_output, p_phone_output, p_email_output
            FROM bb_shopper
            WHERE lastname = p_id_input;
    END;  
END;

Package Body

DECLARE
  lv_name_txt VARCHAR2(20);
  lv_city_txt VARCHAR2(15);
  lv_state_txt VARCHAR2(2);
  lv_phone_txt VARCHAR2(12);
  lv_email_txt VARCHAR2(15);
BEGIN
  shop_query_pkg.shop_lookup_pp(23,lv_name_txt,lv_city_txt,
    lv_state_txt,lv_phone_txt,lv_email_txt);
  DBMS_OUTPUT.PUT_LINE(lv_name_txt);
  DBMS_OUTPUT.PUT_LINE(lv_city_txt);
  DBMS_OUTPUT.PUT_LINE(lv_state_txt);
  DBMS_OUTPUT.PUT_LINE(lv_phone_txt);
  DBMS_OUTPUT.PUT_LINE(lv_email_txt);
  shop_query_pkg.shop_lookup_pp('Ratman',lv_name_txt,lv_city_txt,
    lv_state_txt,lv_phone_txt,lv_email_txt);
  DBMS_OUTPUT.PUT_LINE(lv_name_txt);
  DBMS_OUTPUT.PUT_LINE(lv_city_txt);
  DBMS_OUTPUT.PUT_LINE(lv_state_txt);
  DBMS_OUTPUT.PUT_LINE(lv_phone_txt);
  DBMS_OUTPUT.PUT_LINE(lv_email_txt);
END;

Error Report

Error report -
ORA-06550: line 8, column 3:
PLS-00307: too many declarations of 'SHOP_LOOKUP_PP' match this call
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 15, column 3:
PLS-00307: too many declarations of 'SHOP_LOOKUP_PP' match this call
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Best answer

This error usually happens when you have the same datatypes in the same order as arguments - when this happens you probably have declared the argument names differently so you would specify them in your calls.

In your case, it looks like it's happening because Oracle would have to do implicit conversion on one of your arguments in order to satisfy one of the procedure specifications.

p_name_output OUT bb_shopper.idshopper%TYPE
...
lv_name_txt VARCHAR2(20);

Should the type of p_name_output be declared as a different datatype (I would guess that idshopper is a number data type.

That said, I strongly suggest renaming your p_id_input in the second definition to differentiate lastname and idshopper filtering. Then explicitly use these arguments in your call.

Another answer

PL/SQL lets you overload subprogram names and type methods. You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family. For an example of an overloaded procedure in a package.

An example could be:

DECLARE

   /* First version takes a DATE parameter. */

   FUNCTION value_ok (date_in IN DATE) RETURN BOOLEAN IS
   
BEGIN
      
RETURN date_in <= SYSDATE;
   
END;

/* Second version takes a NUMBER parameter. */

 FUNCTION value_ok (number_in IN NUMBER) RETURN BOOLEAN  IS
   
BEGIN
     
 RETURN number_in > 0;
  
 END;

/* Third version is a procedure! */

PROCEDURE value_ok (number_in IN NUMBER) IS
   
BEGIN
      
IF number_in > 0 THEN 
       
  DBMS_OUTPUT.PUT_LINE (number_in || 'is OK!');
      
ELSE
         DBMS_OUTPUT.PUT_LINE (number_in || 'is not OK!');
     
 END IF;
   
END;

Another answer

As pointed out by Andrew Sayer, the data type of the output variables for the procedures within the package required implicit conversion and were the cause of the the PS-00307 error. Converting them to explicit variables solves this issue.

CREATE OR REPLACE PACKAGE shop_query_pkg IS
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.idshopper%TYPE,
        p_name_output OUT VARCHAR2,        
        p_city_output OUT VARCHAR2,
        p_state_output OUT VARCHAR2,
        p_phone_output OUT VARCHAR2,
        p_email_output OUT VARCHAR2); 
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.lastname%TYPE,
        p_name_output OUT VARCHAR2,        
        p_city_output OUT VARCHAR2,
        p_state_output OUT VARCHAR2,
        p_phone_output OUT VARCHAR2,
        p_email_output OUT VARCHAR2); 
END;
/
CREATE OR REPLACE PACKAGE BODY shop_query_pkg IS
    PROCEDURE shop_lookup_pp
        (p_id_input IN bb_shopper.idshopper%TYPE,
        p_name_output OUT VARCHAR2,        
        p_city_output OUT VARCHAR2,
        p_state_output OUT VARCHAR2,
        p_phone_output OUT VARCHAR2,
        p_email_output OUT VARCHAR2)
        IS
        BEGIN
            SELECT firstname || lastname AS "Name", city, state, phone, email
            INTO p_name_output, p_city_output, p_state_output, p_phone_output, p_email_output
            FROM bb_shopper
            WHERE idshopper = p_id_input;
    END;  
    
    PROCEDURE shop_lookup_pp
            (p_id_input IN bb_shopper.lastname%TYPE,
        p_name_output OUT VARCHAR2,        
        p_city_output OUT VARCHAR2,
        p_state_output OUT VARCHAR2,
        p_phone_output OUT VARCHAR2,
        p_email_output OUT VARCHAR2)
        IS
        BEGIN
            SELECT firstname || lastname AS "Name", city, state, phone, email
            INTO p_name_output, p_city_output, p_state_output, p_phone_output, p_email_output
            FROM bb_shopper
            WHERE lastname = p_id_input;
    END;  
END;

Your Location is: Home > Sql