Re: parameterized ref cursor

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Sep 1999 06:42:06 -0400
Message-ID: <+RjiN6+BD50z3Q4CMAJLXeUy9VSi@4ax.com>

A copy of this was sent to paul cluiss <paul_cluiss_at_intervoice.com> (if that email address didn't require changing) On Thu, 16 Sep 1999 16:07:24 -0500, you wrote:

>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;
>

you do not use :bindvariable names in PL/SQL, you would just code:

  BEGIN
    OPEN theCursor FOR

        SELECT criteria_id, spid
        FROM frd_criteria
        WHERE spid IN ( spidList );

    RETURN theCursor;
  END open_test_cursor;

(plsql 'autobinds' when you refer to a variable in a query -- plsql will bind for you) but -- I think you expect to put '1,2,3' in spidList and have the above evaluate as though it were:

  BEGIN
    OPEN theCursor FOR

        SELECT criteria_id, spid
        FROM frd_criteria
        WHERE spid IN ( 1, 2, 3 );

    RETURN theCursor;
  END open_test_cursor;

but it won't. It would find all rows such that SPID = '1,2,3', *not* rows where SPID = 1 or SPID = 2 or SPID = 3.

If you are using Oracle8i release 8.1 and don't care about using bind variables -- you can code instead:

  BEGIN
    OPEN theCursor FOR

        'SELECT criteria_id, spid
           FROM frd_criteria
          WHERE spid IN (' || spidList ')';

    RETURN theCursor;
  END open_test_cursor;

If you are using Oracle8.0 or up, you can use the following generic solution as well:

tkyte_at_8.0> create or replace type LovType as table of varchar2(2000)   2 /

Type created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace function List_Of_Values( p_str in varchar2 ) return LovType
  2 as

  3      l_x      LovType := LovType();
  4      l_str    varchar2(4001) default p_str || ',';
  5      n        number;
  6  begin
  7      loop
  8          n := instr( l_str, ',' );
  9          exit when (nvl(n,0) = 0);
 10  
 10          l_x.extend;
 11          l_x(l_x.count) := substr( l_str, 1, n-1 );
 12          l_str := substr( l_str, n+1 );
 13      end loop;
 14      return l_x;

 15 end;
 16 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> variable x refcursor
tkyte_at_8.0> variable y varchar2(25)
tkyte_at_8.0> 
tkyte_at_8.0> exec :y := '0,1,2,3,4,5,6,7,8,9,10'

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> declare
  2 type refCur is ref cursor;
  3 begin

  4      open :x for
  5          select *
  6            from all_users
  7           where user_id in
  8                  ( select  *
  9                      from THE ( select cast( List_Of_Values(:y) as LovType )
 10                                   from dual ) );

 11 end;
 12 /

PL/SQL procedure successfully completed.

tkyte_at_8.0>
tkyte_at_8.0> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 20-AUG-99
SYSTEM                                  5 20-AUG-99

>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
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 05:42:06 CDT