comma separated values in oracle function body
- by dmitry
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??
