SQL & PL/SQL

I would like to have opinions on good ways to process rows, based on a provided list of key values, joining the collected list against a source table to retrieve additional information related to the key. In this simple example, the procedure accepts a list of employee numbers. The goal is to print a list of names associated with those numbers. The method is to materialize the list of employee numbers as rows and join those rows to a source table to get the names. I have used BULK COLLECT. I don't know if this is a good approach and I would value suggestions. I would also like to understand why we cannot cast PLSQL tables using a type defined in the procedure's specification (why the type needs to exist as an object before we can cast it, like this:

SELECT * FROM TABLE ( CAST ( SOME_FUNCTION(&some parameter) AS SOME_TYPE ) );

Anyway, here is my demo SQL, which you should be able to execute against the SCOTT schema without any changes. Thanks for your help!

declare

type employee_numbers is table of emp.empno%type index by binary_integer;

type employee_names is table of emp.ename%type index by binary_integer;

type employees_record is record (empno employee_numbers, person_name employee_names);

records employees_record;

employees_cursor sys_refcursor;

employee_number_list varchar2(30) default '7369,7499,7521';


begin

open employees_cursor for

with t as (
select regexp_substr(employee_number_list, '[^,]+', 1, level) as employee_number
from dual
connect by regexp_substr(employee_number_list, '[^,]+', 1, level) is not null
) select emp.empno, emp.ename
from t join emp on (emp.empno = t.employee_number)
order by 2
;


fetch employees_cursor bulk collect into records.empno, records.person_name;

dbms_output.put_line('number of records: '||records.empno.count());

for i in 1 .. records.empno.count

loop

dbms_output.put_line(chr(39)||records.empno(i)||chr(39)||','||chr(39)||records.person_name(i)||chr(39));

end loop;

end;