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

Can't be done as you are trying it. You must use native dynamic SQL.

But to be perfectly honest the code sample you posted is in desparate need of help from someone that knows Oracle. In a cursor FOR loop you don't use FETCH and EXIT WHEN and you don't OPEN the cursor. And even if you did you should have thought to close it at the end of the loop.

The syntax your loop needs is:

FOR xio_record IN (SELECT ...)
LOOP
   dbms_output.put_line('cursor=' || v_a|| ' **** ' ||v_b); END LOOP; And dbms_output.put_line does not belong in production code.

To write the NDS is more work than belongs in my resonse so I leave it to you to find out how at http://tahiti.oracle.com or find someone that knows something about Oracle.

And if you do use NDS you will need to use a REF CURSOR not the FOR LOOP construct above.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)