Re: Dynamic "In" w a Cursor in a Package

CapCity wrote:
> I'm pretty new to Oracle and PL-SQL, I have a SQL Server/T-SQL background.
> I've googled for this and found some examples, but no exact luck yet.
>
> We're on version 9.
>
> We have a need, in a package, to execute a Select statement using an In List
> that will be a paramter. I've been able to successfully use this if the In
> list has one item:
>
> function fcn(include in varchar2) return ref_cursor is
> rc ref_cursor;
>
> begin
> open rc for
> select field1 from table
> where field2 in include and
> field1 not in (select ...);
>
> return rc;
> end;
>
>
> field1 is numeric. As I said, works fine if include contains 1 element
> (include is a comma delimited string). Once it gets a second item, no more
> matches. I presume it is taking it as one item and not using it as a
> comma-separated list of items.
>
> I've seen an example, at
> http://www.oracle-base.com/articles/misc/DynamicInLists.php but I couldn't
> get it to work. I first got the "can't use a local collection" error so I
> switched it to use a type declared in the package. I then got a "missing
> right parenthesis" error, which made no sense. All my parenthesis matched. I
> added a few dozen right ones and got the same message.
>
>
> Since this works with one item in the include, I'm using it that way,
> parsing out the list and calling this separately for each one. It's as slow
> as could be, which I expect. but it's limping out the results. Can anyone
> help me so that it will accept a list of values and treat it as such?
>
> Thanks,

This is possibly one of the most asked about capabilities and I find myself referring people to this demo more often than any other.

Go to www.psoug.org
Click on Morgan's Library
Click on Conditions
Scroll down to "Complex IN Demo"

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org