Oracle SQL function - wrong data type in function call

Question

I have a function inside my package that is meant to split up a comma-separated varchar2 input into rows, ie. 'one, two, three' into:

1. one
2. two
3. three

I have declared the function as:

function unpack_list(
  string_in in varchar2
) return result_str 
is
  result_rows result_str;
begin
  
  with temp_table as (
  SELECT distinct trim(regexp_substr(string_in, '[^,]+', 1, level)) str
  FROM (SELECT string_in FROM dual) t
  CONNECT BY instr(string_in, ',', 1, level - 1) > 0)
  
  select str bulk collect into result_rows from temp_table;
  
  RETURN result_rows;
  
end;

and the return type as:

type result_str is table of varchar2(100);

However, calling the function like:

select * from unpack_list('one1, two2')

gives the following error:

ORA-00902: Invalid datatype

any ideas what causes this?

Answer 1

The reason of the error was described earlier, so I will post another possible solution. For Oracle 19c (version 19.7) and above you may skip creation of table type and use SQL_MACRO addition. Returned query will be integrated into the main query.

create function unpack_list (
  string_in varchar2
)
return clob
sql_macro(table)
is
begin
  
  return q'[
    select distinct
      trim(regexp_substr(
        unpack_list.string_in,
        '[^,]+', 1, level
      )) as str
    from dual
    connect by
      instr(
        unpack_list.string_in,
        ',', 1, level - 1
      ) > 0
  ]';
  
end;
/

select *
from unpack_list(
  string_in => 'one,two'
)

| STR |
| :-- |
| one |
| two |

db<>fiddle here