Mass collection table function throws invalid data type

Question

I am writing a function and I want to wrap it with a table function so that I can use it with select query.

Here is my type declaration and some lines of my functions

CREATE OR REPLACE PACKAGE TYPES AS TYPE CURSORTYPE IS REF CURSOR; TYPE vbugsrec IS RECORD ( bug_id bugs.bug_id%TYPE, facility bugs.facility%TYPE ); TYPE vbugstable IS TABLE OF vbugsrec INDEX BY BINARY_INTEGER; END; / CREATE OR REPLACE PACKAGE BODY CustomQueries AS FUNCTION pendverifylist (myldapid IN userpass.ldapalias%TYPE, maxrows IN PLS_INTEGER:= CustomQueries.maxrecords) RETURN types.vbugstable IS datarows types.vbugstable; var_useralias userpass.ldapalias%TYPE := UPPER (pendverifylist.myldapid) ; CURSOR pendverify_cur ( cursor_var_alias IN userpass.ldapalias%TYPE, cursor_var_mybugstatus IN bugs.bug_status%TYPE, cursor_var_wild IN qa_list.component%TYPE ) IS SELECT buglist.bug_id, buglist.facility FROM bugs buglist, (SELECT qa.product, qa.component FROM qa_list qa, userpass UP WHERE qa.qa_id = UP.userid AND UP.ldapalias = cursor_var_alias) plist WHERE buglist.bug_status = cursor_var_mybugstatus AND buglist.smr_state IN (SELECT fs.finalstate FROM finalstates fs) AND buglist.facility = plist.product AND (buglist.product LIKE plist.component OR plist.component = cursor_var_wild); BEGIN OPEN pendverifylist.pendverify_cur (cursor_var_alias => pendverifylist.var_useralias, cursor_var_mybugstatus => CustomQueries.default_bugstatus, cursor_var_wild => CustomQueries.wildcard); FETCH pendverifylist.pendverify_cur BULK COLLECT INTO pendverifylist.datarows LIMIT LEAST (GREATEST (0, pendverifylist.maxrows), CustomQueries.MAXRECORDS); CLOSE pendverifylist.pendverify_cur; RETURN pendverifylist.datarows; END pendverifylist; END CustomQueries; / 

When I want to use the TABLE function as shown below, I get error.ORA-00902: invalid data type

 SELECT * FROM TABLE(CUSTOMQUERIES.PENDVERIFYLIST ( 'product', 50 )); 

Can anyone help what I am doing wrong here?

Thanks in advance

Answer 1

You are trying to use package level types in plain SQL, which is not valid. Types declared in the package are not visible or valid outside of PL / SQL (or even in simple SQL statements in PL / SQL). An abridged version of what you are doing:

 create or replace package types as type my_rec_type is record (dummy dual.dummy%type); type my_table_type is table of my_rec_type index by binary_integer; end types; / create or replace package p42 as function get_table return types.my_table_type; end p42; / create or replace package body p42 as function get_table return types.my_table_type is my_table types.my_table_type; begin select * bulk collect into my_table from dual; return my_table; end get_table; end p42; / select * from table(p42.get_table); SQL Error: ORA-00902: invalid datatype 

Even in a package, if you had a procedure that tried to use a table function, it would be an error. If you added:

  procedure test_proc is begin for r in (select * from table(get_table)) loop null; end loop; end test_proc; 

... compilation of the package body will end with ORA-22905: cannot access rows from a non-nested table item .

You need to declare types at the schematic level, not in the package, so use the create type command:

 create type my_obj_type is object (dummy varchar2(1)); / create type my_table_type is table of my_obj_type; / create or replace package p42 as function get_table return my_table_type; end p42; / create or replace package body p42 as function get_table return my_table_type is my_table my_table_type; begin select my_obj_type(dummy) bulk collect into my_table from dual; return my_table; end get_table; end p42; / select * from table(p42.get_table); DUMMY ----- X 

Answer 2

In fact, there is no need to have types at the schema level. All you have to do is define the function as PIPELINED.

 -- DEFINITION IN PCKG HEADER create or replace PACKAGE "AAA" IS TYPE t_record IS RECORD ( aaa VARCHAR(20 CHAR), bbb VARCHAR(50 CHAR), ccc VARCHAR(10 CHAR) ); TYPE t_collection is table of t_record; FUNCTION get_records(p_in1 DATE, p_in2 DATE) RETURN t_collection PIPELINED; END AAA; -- PCKG BODY create or replace PACKAGE BODY AAA AS FUNCTION get_records(p_in1 DATE, p_in2 DATE) RETURN t_collection PIPELINED AS CURSOR k1 is SELECT aaa,bbb,ccc FROM table; BEGIN FOR rec IN k1 LOOP pipe row( (rec) ); END LOOP; END get_records END AAA; -- CALLING FUNCTION OUTSIDE OF PCKG select * from TABLE(AAA.get_records(par1, par2)); 

Answer 3

Thanks Alex Poole. This is what I ended up with

 CREATE OR REPLACE TYPE vbugsrec IS OBJECT ( bug_id NUMBER(9), facility VARCHAR2(256) ); CREATE OR REPLACE TYPE vbugstable IS TABLE OF vbugsrec; / CREATE OR REPLACE PACKAGE BODY CustomQueries AS FUNCTION pendverifylist (myldapid IN userpass.ldapalias%TYPE, maxrows IN PLS_INTEGER:= CustomQueries.maxrecords) RETURN vbugstable IS datarows vbugstable := vbugstable(); var_useralias userpass.ldapalias%TYPE:= UPPER (pendverifylist.myldapid) ; TYPE temp_rec IS RECORD ( bug_id bugs.bug_id%TYPE, facility bugs.facility%TYPE ); TYPE temp_records IS TABLE OF temp_rec INDEX BY BINARY_INTEGER; temporary_records temp_records; CURSOR pendverify_cur ( cursor_var_alias IN userpass.ldapalias%TYPE, cursor_var_mybugstatus IN bugs.bug_status%TYPE, cursor_var_wild IN qa_list.component%TYPE ) IS SELECT buglist.bug_id, buglist.facility FROM bugs buglist, (SELECT qa.product, qa.component FROM qa_list qa, userpass UP WHERE qa.qa_id = UP.userid AND UP.ldapalias = cursor_var_alias) plist WHERE buglist.bug_status = cursor_var_mybugstatus AND buglist.smr_state IN (SELECT fs.finalstate FROM finalstates fs) AND buglist.facility = plist.product AND (buglist.product LIKE plist.component OR plist.component = cursor_var_wild); BEGIN OPEN pendverifylist.pendverify_cur (cursor_var_alias => pendverifylist.var_useralias, cursor_var_mybugstatus => CustomQueries.default_bugstatus, cursor_var_wild => CustomQueries.wildcard); FETCH pendverifylist.pendverify_cur BULK COLLECT INTO temporary_records LIMIT LEAST (GREATEST (0, pendverifylist.maxrows), CustomQueries.MAXRECORDS); CLOSE pendverifylist.pendverify_cur; IF temporary_records.COUNT <> 0 THEN FOR rec_idx IN temporary_records.FIRST .. temporary_records.LAST LOOP datarows.EXTEND; datarows (datarows.LAST) := vbugsrec (temporary_records (rec_idx).bug_id, temporary_records (rec_idx).facility); END LOOP; END IF; RETURN pendverifylist.datarows; END pendverifylist; END CustomQueries; /