Dynamic "In" w a Cursor in a Package

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, Received on Fri Sep 21 2007 - 15:56:46 CDT