oracle - Table function with bulk collection throws invalid datatype

Question

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

Here is my type declaration and the 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 TABLE function like below, i get error.ORA-00902: invalid datatype

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

Can anyone please help what i am doing wrong here?

Thanks in advance

Answer 1

You're trying to use package-level types in plain SQL, which isn't allowed. The types declared in the package are not visible to or valid outside PL/SQL (or even in plain SQL statements within PL/SQL). A cut-down version of what you're 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 within the package, if you had a procedure that tried to use the table function it would error. If you added:

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

... the package body compilation would fail with ORA-22905: cannot access rows from a non-nested table item.

You need to declare the types at schema level, not in a package, so using the SQL 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