Pl/SQL question about a parse routine that returns a nested table given a string

From: arudatar <arudatar_at_hotmail.com>
Date: 14 Oct 2004 15:30:43 -0700
Message-ID: <31ca5ffb.0410141430.47364057@posting.google.com>

I am getting
22/2 PL/SQL: SQL Statement ignored
24/59 PL/SQL: ORA-00902: invalid datatype at the below line

from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) );

in the below program.

I am on Oracle 9i
Can anybody let me know as to what is wrong here. Thanks.

create or replace package test_in IS

type myTableType IS table of number;
function str2tbl(p_str in varchar2 ) RETURN myTableType; procedure start_test_in(o_cursor OUT c_get_data); end test_in;

/

create or replace package body test_in IS

function str2tbl( p_str in varchar2 ) return myTableType

   as

      l_str   long default p_str || ',';
      l_n        number;
      l_data    myTableType := myTabletype();

begin

   loop
   l_n := instr( l_str, ',' );
   exit when (nvl(l_n,0) = 0);

   l_data.extend;
   l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
   l_str := substr( l_str, l_n+1 );

   end loop;
   return l_data;
end;

procedure start_test_in(o_cursor OUT c_get_data) IS BEGIN
open o_cursor FOR
 select * from all_users
   where user_id in ( select *
   from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) );
END start_test_in;

END test_in; Received on Thu Oct 14 2004 - 17:30:43 CDT