CODEWITHSUNDEEP
oracle-databaseplsql
Paul Stearns
Paul Stearns

Reputation: 906

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

It is my understanding that you cannot use a collection in a where clause unless it is defined at the DB level. I have a distinct dislike for random type definitions laying about a schema. It's a religious thing so don't try to dissuade me.

Types contained within a package are cool, because they are easily found and are related to the work at hand. So having said that I have a package that defines a structure (currently a table type collection) that looks like;

  TYPE WORD_LIST_ROW IS RECORD(
    WORD VARCHAR(255));
  TYPE WORD_LIST IS TABLE OF WORD_LIST_ROW;

There is a routine in the package that instantiates and populates an instance of this. It would be useful to be able to use the instantiated object, or some analog therof in a where clause.

So being the clever (or so I thought) programmer, I said why don't I just create a pipelined function to make a table from the collection which I did, and it looks like;

  FUNCTION WORD_LIST_TABLE(IN_WORD_LIST WORD_LIST) RETURN WORD_LIST PIPELINED
  AS
    OUT_WORD_LIST WORD_LIST := WORD_LIST();
  BEGIN
    FOR I IN 1 .. IN_WORD_LIST.COUNT
    LOOP
      PIPE ROW(IN_WORD_LIST(I));
    END LOOP;
    RETURN;
  END WORD_LIST_TABLE;

Then in another routine I call the function that builds the collection, finally I use a pipelined function that uses the collection as input in a cursor's where clause.

sort of like this;

cursor xyz
is 
    select * from x-stuff where fieldA in (select word from table(word_list_table(temp_word_list));

In the loop for the cursor I get an oracle error ora-21700 object does not exist or is marked for delete.

Is there any easy way to build an oracle object that can be used in an Oracle where clause? Basically what I would like to do is;

select * from whatever where fielda in myobject;

Upvotes: 2

Views: 3162

Answers (1)

Rachcha
Rachcha

Reputation: 8816

The solution is simple - declare the type at schema level using CREATE TYPE statement and you will be able to use your collections in your SQL statements in PL/SQL blocks.

If you have declared your TYPE inside a PL/SQL package you cannot use it in your queries inside PL/SQL blocks.

Also, you must keep in mind that only varray and nested table type collections can be used in queries as of Oracle 11.2 and you cannot use associative arrays in queries.. In 12c you don't have these restrictions.

For further reference go to Oracle Docs.

Upvotes: 1

Related Questions