CODEWITHSUNDEEP
sqloracle-databaseplsql
Dennis M.
Dennis M.

Reputation: 107

wrong numbers or types of argument when calling a function from package (oracle, sql)

So, I got two functions with the same name in a package.

create or replace package body employee_pkg
as
    function fullname(
        last_in employees.last_name%type,
        first_in employees.first_name%type
    )
        return fullname_t
    is
    begin
        return last_in || ', ' || first_in;
    end;
    
    function fullname(employee_id_in in employees.employee_id%type)
        return fullname_t
    is
        l_fullname fullname_t;
    begin
        select fullname(last_name, first_name) into l_fullname
          from employees
         where employee_id = employee_id_in;
    
        return l_fullname;
    end;
end employee_pkg;
/

Fun1: get Last- & First- name (parameter) and return the Fullname
Fun2: get ID -> get Last- & First- name from table where ID and return the Fullname

, and everytime I try to call the function I get the error:

PLS-00306: wrong number or types of arguments in call to 'FULLNAME'

I tried calling the function like this: (method with Last- & First- name)

declare
    last_in employees.last_name%type;
    first_in employees.first_name%type;

begin
    last_in := 'Lastname';
    first_in := 'Firstname';
    
    employee_pkg.fullname(last_in, first_in);
end;
/

and also like this: (method with ID)

declare
    eID employees.employee_id%type;
begin
    eID := 1;
    
    employee_pkg.fullname(eID);
end;
/

To test it out I use the HR user.

In my opinion everything should work as intended, but something is obviously wrong and I tried to figure out what the problem is for a while know.

Thank you in advance for helping me finding a solution for my problem.

P.S.: The code snippet is from an article from the oracle-magazine written from Steven Feuerstein. Link

Upvotes: 1

Views: 221

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6064

In your package body, both definitions of fullname are FUNCTIONS so the value being returned needs to be stored in a variable when it is called.

When you are calling the procedures, try calling them like this:

DECLARE
    last_in       employees.last_name%TYPE;
    first_in      employees.first_name%TYPE;

    eID           employees.employee_id%TYPE;

    l_full_name   fullname_t;
BEGIN
    last_in := 'Lastname';
    first_in := 'Firstname';

    l_full_name := employee_pkg.fullname (last_in, first_in);

    eID := 1;

    l_full_name := employee_pkg.fullname (eID);
END;
/

Upvotes: 1

Related Questions