sql - Why the table of my function is not acceptable? -

create or replace package pkg type result_t   table of varchar2(3000); function generatef   return result_t ; end pkg; / create or replace package body pkg function generatef   return result_t   i_t result_t := result_t(); begin    tlc in 1..3   loop     i_t.extend;     i_t(i_t.last) := tlc;   end loop;   return i_t; end; end pkg; / 

when want execute "select * table(pkg.generatef);", system tells me there "invalid datatype". i'm not sure brings issue.

this type result_t not globally defined, that's why oracle (or dbms) not identify type , not able cast output table form.

you can specify type outside package -

create or replace type result_t   table of varchar2(3000); 

and remove definition type package -

create or replace package pkg   /*type result_t       table of varchar2(3000);*/   function generatef return result_t; end pkg; / create or replace package body pkg   function generatef return result_t     i_t result_t := result_t();   begin      tlc in 1 .. 3 loop       i_t.extend;       i_t(i_t.last) := tlc;     end loop;     return i_t;   end; end pkg; / 

and when query -

select * table(pkg.generatef); result desired.