parameterized ref cursor

Hello everyone.

I have a PL/SLQ cursor variable which I would like to parameterize by letting it examine the value of an input variable (spidList), but Oracle keeps giving me an error indicating the bound variable is not right.

Here's the code:

TYPE test_curtype IS REF CURSOR RETURN frd_criteria_rowtype;

FUNCTION open_test_cursor (spidList IN VARCHAR2) RETURN test_curtype
IS
  theCursor test_curtype;
  BEGIN
    OPEN theCursor FOR

        SELECT criteria_id, spid
        FROM frd_criteria
        WHERE spid IN :spidList;

    RETURN theCursor;
  END open_test_cursor;

Here's the error message:

19/18    PLS-00049: bad bind variable 'SPIDLIST'
19/18    PLS-00103: Encountered the symbol "" when expecting one of the
         following:
         (

510/1 PLS-00103: Encountered the symbol "END" when expecting one of the

         following:
         begin function package pragma procedure form external

Note: The line numbers are off because I've got this in a package, but only wanted to include the fragment.

I was wondering if anyone knows how to use a bind variable for the select part of a cursor variable.

If I can't get it working I think I'll use a parameterized static cursor, but I don't really want to do that.

Thank you for your help.

Paul Cluiss
Dallas, Texas Received on Thu Sep 16 1999 - 16:07:24 CDT