Passing values to IN clause on a function oracle

Question

I need to return a cursor within a function:

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN cursor

IS
  row_test table_colors := table_colors(bigstring);
  c1 CURSOR;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                    from table(row_test));

   RETURN c1;

END test_cursor;

table_colors is:

create or replace type table_colors as table of varchar2(20);

But when I test it passing like blue, red, pink, white or 'blue', 'red', 'pink', 'white' always throws the same error

ORA-06502: PL/SQL; numeric or value error: character string buffer too small

on this line row table_colors := table_colors(bigstring);

What I am doing wrong here?

Answer 1

The problem is that bigstring is a single scalar value that may happen to contain commas and single quotes not a list of values. You would need to parse the string to extract the data elements. If each of the individual elements within bigstring happens to be a valid Oracle identifier, you could use the built-in dbms_utility.comma_to_table function. Were it my system, though, I'd feel more comfortable with my own parsing function. Assuming that bigstring is just a comma-separated list, I'd use a version of Tom Kyte's str2tbl function

create or replace function str2tbl( p_str in varchar2 ) 
  return table_colors
as
  l_str   long default p_str || ',';
  l_n        number;
  l_data    table_colors := table_colors();
begin
  loop
    l_n := instr( l_str, ',' );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  end loop;
  return l_data;
end;

Now, you can realistically implement str2tbl using regular expressions in a single SQL statement as well. That might be a touch more efficient. I'd expect, however, that string parsing is well down on your list of performance issues so I would tend to stick with the simplest thing that could possibly work.

Your procedure would then become

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN sys_refcursor
IS
  row_test table_colors := str2tbl(bigstring);
  c1 sys_refcursor;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                           from table(row_test));

   RETURN c1;

END test_cursor;