Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Dynamic "In" w a Cursor in a Package

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

From: DA Morgan <>
Date: Sun, 23 Sep 2007 09:27:23 -0700
Message-ID: <>

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
> 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
Click on Morgan's Library
Click on Conditions
Scroll down to "Complex IN Demo"

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sun Sep 23 2007 - 11:27:23 CDT

Original text of this message