comma separated values in oracle function body

Question

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

Answer 1

You don't give us the actual error, which makes it harder for us to diagnose your problem. However, it is worth a punt: ORA-00902: invalid datatype

You haven't implemented Tom's solution exactly as he gave it. Specifically, he created myTableType as a SQL Type whereas you have declared it in the package specification. This is not a trivial detail: we cannot use PL/SQL types in SQL statements. Hence the exception.

So, remove the declration of MyTableType from the package and create it in SQL....

create or replace type mytabletype as table of varchar2(255);
/

You SELECT statement should now work. If it doesn't please edit your question to give us the exact error message.

edit

"I want everything to be inside the package. What do I have to change to accomplish that?"

Here is a kluge. As you can see, PKG1 declares the PL/SQL type in the spec:

SQL> create or replace package pkg1 as
  2      TYPE myTableType IS table of varchar2 (255);
  3      function split (p_string  in    long )
  4          return          myTableType ;
  5      function get_resultset (p_tab in myTableType)
  6          return sys_refcursor;
  7      function get_resultset_for_str (p_string  in    long)
  8          return sys_refcursor;
  9  end pkg1;
 10  /

Package created.

SQL>

In the package body you will recognise SPLIT() as Tom Kyte's solution. GET_RESULTSET() loops through a passed collection and assembles a dynamic SQL statement. GET_RESULTSET_FOR_STR() is a helper funnction which calls both the other functions.

SQL> create or replace package body pkg1 as
  2      function split (p_string   in     long )
  3      return          myTableType
  4      is
  5              l_string        long default p_string || ',';
  6              l_data          myTableType := myTableType();
  7              n               number;
  8          begin
  9            loop
 10                exit when l_string is null;
 11                n := instr( l_string, ',' );
 12               l_data.extend;
 13               l_data(l_data.count) :=
 14                       ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
 15               l_string := substr( l_string, n+1 );
 16          end loop;
 17          return l_data;
 18      end split;
 19
 20      function get_resultset (p_tab in myTableType)
 21          return sys_refcursor
 22      is
 23          return_value sys_refcursor;
 24          stmt varchar2(32767);
 25          i pls_integer := 1;
 26      begin
 27          stmt := 'select '''||p_tab(1)||''' from dual';
 28          while i < p_tab.count()
 29          loop
 30              i := i+1;
 31              stmt := stmt||' union all select '''||p_tab(i)||''' from dual';
 32          end loop;
 33          open return_value for stmt;
 34          return return_value;
 35      end get_resultset;
 36
 37      function get_resultset_for_str (p_string  in    long)
 38          return sys_refcursor
 39      is
 40          l_tab myTableType;
 41          return_value sys_refcursor;
 42      begin
 43          l_tab := split(p_string);
 44          return_value :=  get_resultset (l_tab);
 45          return return_value;
 46      end get_resultset_for_str;
 47
 48  end pkg1;
 49  /

Package body created.

SQL>

Here is it working in SQL*Plus:

SQL> var rc refcursor
SQL> exec :rc := pkg1.get_resultset_for_str('ABC,DEF,XYZ')

PL/SQL procedure successfully completed.

SQL> print rc

'AB
---
ABC
DEF
XYZ

SQL>