PLSQL - How to retrieve values into a collection given an array of values?

Question

I have a procedure that accepts an array of folder IDs and needs to return a list of document IDs. Folders are associated to documents in a one-to-many relationship--there are many documents for each folder. Specifically, there is a documents table which has a parent_folderid fk to the folders table.

This is what I have:

PROCEDURE get_folder_documents_ (
    paa_folderids_i IN gtyp_folderids_table
) IS                                                                                 
    lnt_temp_docids       &&MATTER_SCHEMA..docid_tab := &&MATTER_SCHEMA..docid_tab();
    lv_current_table_size NUMBER := gnt_documentids.COUNT;
BEGIN                                                     
    FOR i IN paa_folderids_i.FIRST .. paa_folderids_i.LAST
    LOOP                 
        SELECT documentid
          BULK COLLECT INTO lnt_temp_docids
          FROM t$documents
         WHERE parent_folderid = paa_folderids_i(i);

        FOR j IN 1 .. lnt_temp_docids.COUNT
        LOOP                                                   
            lv_current_table_size := lv_current_table_size + 1;

            gnt_documentids.EXTEND(1);                                   
            gnt_documentids(lv_current_table_size) := lnt_temp_docids(j);
        END LOOP;
    END LOOP;
END get_folder_documents_;

Is there a better way?

Answer 1

If gtyp_folderids_table is declared as a SQL type (as opposed to a PL/SQL type) you could use it in a SQL statement via a table() function like this:

    SELECT documentid
      BULK COLLECT INTO gnt_documentids
      FROM t$documents
     WHERE parent_folderid in ( select * from table( paa_folderids_i)); 

edit

If you want a PL/SQL answer, in 10g there is a more effective way - or at least an approach which requires less typing ;).

Oracle introduced some neat-o set operators which we can use with collections. The following example uses MULTISET UNION to munge several collections into one...

SQL> set serveroutput on size unlimited
SQL>
SQL> declare
  2      v1 sys.dbms_debug_vc2coll
  3          := sys.dbms_debug_vc2coll('SAM I AM', 'FOX IN SOCKS');
  4      v2 sys.dbms_debug_vc2coll
  5          := sys.dbms_debug_vc2coll('MR KNOX', 'GRINCH');
  6      v3 sys.dbms_debug_vc2coll
  7          := sys.dbms_debug_vc2coll('LORAX', 'MAISIE');
  8      v_all sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll();
  9  begin
 10      dbms_output.put_line('V_ALL has '|| v_all.count() ||' elements');
 11      v_all := v1 multiset union v2;
 12      dbms_output.put_line('V_ALL has '|| v_all.count() ||' elements');
 13      v_all := v_all multiset union v3;
 14      dbms_output.put_line('V_ALL has '|| v_all.count() ||' elements');
 15  end;
 16  /
V_ALL has 0 elements
V_ALL has 4 elements
V_ALL has 6 elements

PL/SQL procedure successfully completed.

SQL>

Find out more about collections in 10g.