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.


Comments

Popular posts from this blog

How to remove text and logo OR add Overflow on Android ActionBar using AppCompat on API 8? -

html - How to style widget with post count different than without post count -

url rewriting - How to redirect a http POST with urlrewritefilter -