comma separated values in oracle function body

Posted by dmitry on Stack Overflow See other posts from Stack Overflow or by dmitry
Published on 2010-06-15T23:45:26Z Indexed on 2010/06/15 23:52 UTC
Read the original article Hit count: 267

Filed under:
|

I've got following oracle function but it does not work and errors out. I used Ask Tom's way to convert comma separated values to be used in select * from table1 where col1 in <>

declared in package header:

TYPE myTableType IS table of varchar2 (255);

Part of package body:

l_string        long default iv_value_with_comma_separated|| ',';
l_data          myTableType := myTableType();
n               NUMBER;

begin
  begin
LOOP
    EXIT when l_string is null;
    n := instr( l_string, ',' );
     l_data.extend;
     l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
     l_string := substr( l_string, n+1 );
END LOOP;
end;

OPEN my_cursor FOR
  select * from table_a where column_a in (select * from table (l_data));
CLOSE my_cursor
END;

above fails but it works fine when I remove

select * from table (l_data)

Can someone please tell me what I might be doing wrong here??

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about plsql