PL/SQL Functions in collection type

Question

So I've created an object type with several attributes.

And from that object I've also created a collection type. Let's say:

create or replace type employees as table of employee; 

Now I want to use the collection object to perform methods such as GetAllEmployees(), GetEmployeesByJobType() and so on. So that I would be somewhere else call upon the object type and perform the method:

v_list := employees.GetAllEmployees(); Same as you would be making call from a package but instead of a package I want to call an object.

However I tried specifying and creating the methods just as I did in the object but it would not compile.

Now I know I can execute the logic for the methods in a package but I want to keep it seperate.

So my question is: Is it possible to perform the logic inside the object collection?

Answer 1

This could be an example of what you are looking for:

create or replace type employeeObj as object (id number, name varchar2(100));

create or replace type employeeList is table of employeeObj;

create or replace type employeeListObj as object
(
    listOfEmployees employeeList,
    member procedure GetAllEmployees
); 

create or replace type body employeeListObj is
    member procedure GetAllEmployees is
    begin
        for i in listOfEmployees.first .. listOfEmployees.last loop
            dbms_output.put_line('Emp: ' || listOfEmployees(i).name );
        end loop;
    end;
end; 

How it works:

SQL> declare
  2      vListEmp    employeeList;
  3      vListEmpObj employeeListObj;
  4  begin
  5      vListEmp := employeeList();
  6      vListEmp.extend(2);
  7      vListEmp(1) := employeeObj(1, 'John Smith');
  8      vListEmp(2) := employeeObj(2, 'Steve James');
  9
 10      vListEmpObj := new employeeListObj(vListEmp);
 11
 12      vListEmpObj.GetAllEmployees();
 13  end;
 14  /
Emp: John Smith
Emp: Steve James

PL/SQL procedure successfully completed.