Re: Using the 'IN' Comparison Operator in a Cursor

Darren

You need to use dynamic SQL and split your inlist into separate variables to bind them in individually - you can't bind an entire IN list.

Dynamic method below - Note the in list string gets SUBSTR and INSTR into its constituent parts at bind time:-

DECLARE
   TYPE typ_ref_cursor IS REF CURSOR;
   cur_my_cursor typ_ref_cursor;

   v_sql VARCHAR2(256);
   my_in_list VARCHAR2(128) := 'VALUE1,VALUE2';    v_column table.column%TYPE;

BEGIN    v_sql := 'SELECT column FROM table WHERE value IN (:value1,:value2)';

   /* Opening a dynamic cursor with binding (preferable) */    OPEN cur_my_cursor FOR v_sql
   USING IN SUBSTR(my_in_list,1,INSTR(my_in_list,',')-1),

            SUBSTR(my_in_list,INSTR(my_in_list,',')+1);    LOOP

      FETCH cur_my_cursor INTO v_column;
      EXIT WHEN cur_my_cursor%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(v_column);

Note if you wanted your in list string to be concatenated onto the v_sql string, they would need to contain quotes around the individual values if they were varchar2s eg

      my_in_list := '''VALUE1'',''VALUE2''';    begin

      v_sql := 'SELECT mycolumn FROM mytable WHERE value IN
('||my_in_list||')';

However, if they were numbers then the following would be OK:

      my_in_list := '1,2,3';
   begin

      v_sql := 'SELECT mycolumn FROM mytable WHERE value IN
('||my_in_list||')';

      ...

BUT then you lose the power and scalability of bind variables as you've sent a bunch of literals to the SQL srea.

Hope this helps

Regards

Adrian Received on Fri Mar 08 2002 - 15:08:12 CET