IN LIST in PL/SQL

From: James A. Williams <jwilliam_at_aglresources.com>
Date: 19 Jan 2004 16:48:45 -0800
Message-ID: <5003a2b9.0401191648.2c0facfd@posting.google.com>

I have some anonymous PL/SQL in the below I am testing for a routine eventually to be called from C# application. I am trying to build an IN list in a variable as the below shows and it is not taking it.

I take a string '04,06,08' and try to get it to work as select * from t_table where b IN (V_VARIABLE);

It only works when I hard code the variables. My next step is to look at the 9i pipeline functions if the below or something close will not work.

connect /

set serveroutput on

DECLARE i_VAR varchar2(10);
o_concat varchar2(20);
TABX ora_util.string_array;

v_a  t_table.a%TYPE;
v_b  t_table.b%TYPE;
v_o  number;

xio_cursor sys_refcursor;
--

BEGIN
i_var := '04,06,08';
ora_util.delimited_string_to_array(i_var,',',TABX);

FOR I IN TABX.FIRST..TABX.LAST
 LOOP
    DBMS_OUTPUT.PUT_LINE('vars=' || TABX(i));     o_concat := o_concat||chr(39)||tabx(i)||chr(39)||',';

• o_concat := o_concat||tabx(i)||','; END LOOP; o_concat := rtrim(o_concat,','); DBMS_OUTPUT.PUT_LINE('varo=' || o_concat); DBMS_OUTPUT.PUT_LINE('tab_count=' || tabx.count);

--

OPEN xio_cursor FOR
  SELECT * FROM T_TABLE WHERE B IN (o_concat);

• SELECT * FROM T_TABLE WHERE B IN ('04','08');
  --
  LOOP FETCH xio_cursor INTO v_a,v_b; exit when xio_cursor%NOTFOUND; dbms_output.put_line('cursor=' || v_a|| ' **** ' ||v_b); END LOOP; END; /

Received on Mon Jan 19 2004 - 18:48:45 CST