Object Oriented Oracle PLSQL Tutorial

You do this by casting the collection to a table.

SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
  3  /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL> create or replace type emp2_oty is object (empNo NUMBER,eName VARCHAR2(10))
  2  /
Type created.
SQL>
SQL> create or replace type emp2_nt is table of emp2_oty
  2  /
Type created.
SQL>
SQL> create or replace function f_getEmps_nt(i_deptNo number, i_hireDate DATE)
  2  return emp2_nt
  3  is
  4      v_emp2_nt emp2_nt:=emp2_nt();
  5      cursor c_emp is select * from employee;
  6  begin
  7    for r_emp in c_emp
  8    loop
  9      if i_deptNo is null then
 10        if i_hireDate is null or to_char(i_hireDate,'mm')= to_char(r_emp.start_Date,'mm')
 11        then
 12           v_emp2_nt.extend;
 13           v_emp2_nt(v_emp2_nt.last):= emp2_oty(r_emp.id, r_emp.first_Name);
 14        end if;
 15      elsif i_deptNo=r_emp.id then
 16        v_emp2_nt.extend;
 17        v_emp2_nt(v_emp2_nt.last):= emp2_oty(r_emp.id, r_emp.first_name);
 18      end if;
 19    end loop;
 20    return v_emp2_nt;
 21  end;
 22  /
Function created.
SQL>
SQL> select t.*
  2  from table (cast (f_getEmps_nt (20,null) as emp2_nt)) t
  3  /
no rows selected
SQL>
SQL> create or replace package pkg_globals
  2  is
  3      v_currentDeptno number:=10;
  4      function f_getCurrDeptNo return number;
  5      procedure p_setCurrDeptNo(i_nr number);
  6
  7      v_currentHireDate date:=sysdate;
  8      function f_getCurrHireDate return date;
  9      procedure p_setCurrHireDate(i_dt date);
 10  end;
 11  /
Package created.
SQL>
SQL> create or replace package body pkg_globals
  2  is
  3      function f_getCurrDeptNo return number is
  4      begin
  5          return v_currentDeptNo;
  6      end;
  7      procedure p_setCurrDeptNo(i_nr number) is
  8      begin
  9          v_currentDeptNo:=i_nr;
 10      end;
 11      function f_getCurrHireDate return date is
 12      begin
 13          return v_currentHireDate;
 14      end;
 15      procedure p_setCurrHireDate(i_dt date) is
 16      begin
 17          v_currentHireDate:=i_dt;
 18      end;
 19  end;
 20  /
Package body created.
SQL>
SQL> create or replace view v_getEmps
  2  as
  3  select *
  4  from table(cast (f_getEmps_nt (pkg_globals.f_getCurrDeptNo,pkg_globals.f_getCurrHireDate) as emp2_nt));
View created.
SQL>
SQL> drop type emp2_nt;
Type dropped.
SQL>
SQL> drop type emp2_oty;
Type dropped.
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>