SELECT from a Nested Table - Need our help.

Hello, everyone. I have a question about SELECTing data from a nested table. I am running Oracle 9i. I am using a package that has one function which returns a variable of type TABLE, and one procedure which returns the rows from that table. My source code is below. My main problem is on this:

SELECT * FROM THE(SELECT CAST(deptList AS sa.PTG_ADMIN.DEPT_LIST) FROM DUAL); My function works fine, because I can put DBMS_OUTPUT statements in it and it will print out each row of the table variable. But I can't get the SELECT in my procedure to work correctly. Any help is much appreciated. Thanks!

--Mike Loll

p.s. Oh, you will notice the "SA" schema name ... this DB was migrated from SQL Server and it created the scheme name as "SA". Interesting, I guess. I plan on moving everything to a new schema name soon.

p.s.s. I am a complete Oracle newbie. :)

---
CREATE OR REPLACE PACKAGE "SA"."PTG_ADMIN"  as
 
  TYPE DEPT_LIST IS TABLE OF VarChar2(20);
  
  function makeDeptList(deptDelimList in varchar2, deliminator in
varchar2)
    return sa.PTG_admin.DEPT_LIST;
    
  procedure BudgetsNotInDeptList(deptDelimList in varchar2,
deliminator in varchar2);
  
end;

CREATE OR REPLACE  PACKAGE BODY "SA"."PTG_ADMIN"  as

FUNCTION makeDeptList


(

  deptDelimList in varchar2,
  deliminator in varchar2
) return SA.PTG_ADMIN.DEPT_LIST
IS
  i integer;
  j integer;
  k integer;
  deptList DEPT_LIST;

BEGIN

  i := 1;
  j := 1;
  k := 1;
  
  while k <= length(deptDelimList)
  loop
    j :=  instr(deptDelimList,deliminator,k,1);

    if j = 0 then
    begin
      deptList(i) := substr(deptDelimList,k, length(deptDelimList));
      k := length(deptDelimList) + 1;
    end;
    else
    begin
      deptList(i) := substr(deptDelimList,k, (j- k));
      k := j + 1;
    end;
    end if;

    i := i + 1;

  end loop;
  RETURN deptList;
END MAKEDEPTLIST;

procedure BudgetsNotInDeptList


(

  deptDelimList in varchar2,
  deliminator in varchar2
)
IS
  deptList DEPT_LIST;
begin
  deptList := makeDeptList('0,1,2,3,4,5,6,7,8,9,10', ',');
  
  SELECT * FROM THE(SELECT CAST(deptList AS sa.PTG_ADMIN.DEPT_LIST)
FROM DUAL );

end BudgetsNotInDeptList;
end;