Re: IN LIST in PL/SQL

James A. Williams wrote:
>
> 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;
> /

You can use a collection type to do this...Something like:

type t is table of varchar2(80);

declare
  x t := t('04','08','13');
begin
  for i in (
    select * from t1
    where col in (

      select column_value
      from table(cast(x as t)) )

etc

hth
connor

-- 
-------------------------------
Connor McDonald
http://www.oracledba.co.uk
Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"