Re: select ... where col in (collection)

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in news:1063001949.866577_at_yasure:

> I think you should answer the question I asked you. My sense is
> that you are trying to do something simple in the most convoluted
> manner possible.

Well, I hope that is the case, because that's sure how it feels.

I'm sure you can appreciate that for sake of posting this question, I've tried to reduce the full scope of my problem to a fairly simple and straight forward presentation of the syntax involved. Sure, this may look like a rediculous way to get users from the all_users table, but that isn't really the point...

What I'm (simply) trying to do is to create a PL/SQL function that accepts an unknown number of values (hence the collection type) that it can then use among other various criteria to select rows to operate on, or return as part of a pipeline.

I don't think I can use a ref cursor for what I want here, because the values in question are not the result of a select from some table. They are more likely to be an arbitrary set of values that have come from user input, or some other source.

That said, I believe that the change I showed in the last post:

   where col in (select column_value from table (cvar))

was about the right thing, and now I just need to solve the runtime problem I mentioned previously.
Which, to reiterate here, was

  ORA-22905: cannot access rows from a non-nested table item

How would the use of a REF CURSOR make this easier for me? I'd much appreciate your suggestion.

Thanks. Received on Mon Sep 08 2003 - 11:50:32 CDT