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