plsql - How can I easily use a collection in an Oracle where clause in a package? -

it understanding cannot use collection in clause unless defined @ db level. have distinct dislike random type definitions laying schema. it's religious thing don't try dissuade me.

types contained within package cool, because found , related work @ hand. having said have package defines structure (currently table type collection) looks like;

  type word_list_row record(     word varchar(255));   type word_list table of word_list_row; 

there routine in package instantiates , populates instance of this. useful able use instantiated object, or analog therof in clause.

so being clever (or thought) programmer, said why don't create pipelined function make table collection did, , looks like;

  function word_list_table(in_word_list word_list) return word_list pipelined       out_word_list word_list := word_list();   begin     in 1 .. in_word_list.count     loop       pipe row(in_word_list(i));     end loop;     return;   end word_list_table; 

then in routine call function builds collection, use pipelined function uses collection input in cursor's clause.

sort of this;

cursor xyz      select * x-stuff fielda in (select word table(word_list_table(temp_word_list)); 

in loop cursor oracle error ora-21700 object not exist or marked delete.

is there easy way build oracle object can used in oracle clause? is;

select * whatever fielda in myobject; 

the solution simple - declare type @ schema level using create type statement , able use collections in sql statements in pl/sql blocks.

if have declared type inside pl/sql package cannot use in queries inside pl/sql blocks.

also, must keep in mind varray , nested table type collections can used in queries of oracle 11.2 , cannot use associative arrays in queries.. in 12c don't have these restrictions.

for further reference go oracle docs.