PLS-00382: expression is of wrong type by executing function and try to put the returntype in a variable

Question

When I execute the function I get that the expression is of the wrong type but I don't know why. The return type that I use in the function is the same as where I try to put it in after the function is executed.

Below you find the record and table type.

TYPE department_id_table_type IS TABLE of DEPARTMENTS.DEPARTMENT_ID%TYPE;

TYPE managers_rec_type IS RECORD (
  employee_id   employees.employee_id%TYPE, 
  first_name employees.first_name%TYPE,
  last_name employees.last_name%TYPE,
  department_id_table_type DEPARTMENTS.DEPARTMENT_ID%TYPE);

TYPE managers_table_type IS TABLE OF managers_rec_type INDEX BY BINARY_INTEGER;

Below you find the function

 FUNCTION managers_multiple_departments RETURN managers_table_type 
IS
cursor department_curs is SELECT DEPARTMENT_NAME,MANAGER_ID,DEPARTMENT_ID FROM DEPARTMENTS WHERE MANAGER_ID IN (SELECT MANAGER_ID FROM DEPARTMENTS dep GROUP BY (MANAGER_ID) HAVING COUNT(MANAGER_ID) >1);


    department_name departments.department_name%TYPE;
    department_id departments.department_id%TYPE;
    managerid departments.manager_id%TYPE;
    employeeid employees.employee_id%TYPE;
    firstname employees.first_name%TYPE;
    lastname employees.last_name%TYPE;
    count NUMBER;       
    rec managers_rec_type;            
    managers_rec managers_rec_type;
    teller NUMBER := 1;
    managers_table managers_table_type;         

BEGIN           
    OPEN department_curs;
    LOOP                 
        FETCH department_curs INTO department_name, managerid,department_id;
        EXIT WHEN department_curs%NOTFOUND;
        Select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,department_id into managers_rec from EMPLOYEES where MANAGER_ID = managerid; 
        managers_table(managers_rec.employee_id) := managers_rec;
        FOR i IN managers_table.FIRST .. managers_table.LAST LOOP
            IF managers_table.EXISTS(i) THEN
                DBMS_OUTPUT.PUT_LINE(managers_table(i).first_name);
            END IF;

        END LOOP;

        IF teller = 1 THEN
            DBMS_OUTPUT.PUT_LINE(managers_rec.first_name ||' '|| managers_rec.last_name || ' Lijst van departments:');
            teller := 2;
        END IF;
        DBMS_OUTPUT.PUT_LINE(department_id ||' '|| department_name);


    END LOOP;
        FOR i IN managers_table.FIRST .. managers_table.LAST LOOP
            IF managers_table.EXISTS(i) THEN
                DBMS_OUTPUT.PUT_LINE(managers_table(i).first_name);
                DBMS_OUTPUT.PUT_LINE('test');
            END IF;

        END LOOP; 
    return managers_table;
END managers_multiple_departments;`enter code here`

Below is where I execute the function but this is where it is giving me the error on: managers := hr_package.managers_multiple_departments;

DECLARE
    TYPE managers_rec_type IS RECORD (
      employee_id   employees.employee_id%TYPE, 
      first_name employees.first_name%TYPE,
      last_name employees.last_name%TYPE,
      department_id_table_type DEPARTMENTS.DEPARTMENT_ID%TYPE);

    TYPE managers_table_type IS TABLE OF managers_rec_type INDEX BY BINARY_INTEGER;    
    man_rec managers_rec_type; 
    managers managers_table_type;
    twee NUMBER;

BEGIN

    managers := hr_package.managers_multiple_departments;
END;

Answer 1

You are declaring the record type, collection/table type and function all within the same package.

When you call the function you have to use the same type, from that package.

The return type that I use in the function is the same as where I try to put it in after the function is executed.

But it isn't. It has the same structure - fields and datatypes - but is not the same as far as Oracle is concerned. Oracle needs to know that exactly the same type is being used, partly so that it can keep track of dependencies between objects.

Your anonymous block needs to refer to the package types, rather than declaring its own - similar but conflicting - type(s):

DECLARE
    managers hr_package.managers_table_type;
BEGIN
    managers := hr_package.managers_multiple_departments;
END;

As a bonus it involves much less typing, and means you don't have to manage duplicate types.

It does also mean, though, that the type declarations have to be in the package specification - which is the case for anything you want to be publicly visible, of course.