Re: IN LIST in PL/SQL

From: James Williams <willjamu_at_mindspring.com>
Date: Tue, 20 Jan 2004 10:33:27 GMT
Message-ID: <400d0309.1114234@news.east.earthlink.net>

On Mon, 19 Jan 2004 17:09:12 -0800, Daniel Morgan <damorgan_at_x.washington.edu> wrote:

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

Thank you very much.

The fragment was strictly for testing purposes. We have a common routine for error handling. No dbms_output is in production.

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
   ( select *
     -- from THE ( select cast( in_list(o_concat)
     from THE ( select cast( in_list(:x)
                         as mytableType ) from dual ))' using
o_concat;

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

 
The above also yields the proper results!

Received on Tue Jan 20 2004 - 04:33:27 CST