You are calling a PL/SQL function that returns a PL/SQL collection type (both defined in your package) from a SQL context. You can't do that directly. You can call the function from a PL/SQL context, assigning the result to a variable of the same type, but that isn't how you're trying to use it. db<>fiddle showing your set-up, your error, and it working in a PL/SQL block.
You could declare the type at schema level instead, as @Littlefoot showed:
create type result_str is table of varchar2(100);
and remove the package definition, which would clash; that works for both SQL and PL/SQL (db<>fiddle).
Or if you can't create a schema-level type, you could use a built-in one:
function unpack_list(
string_in in varchar2
) return sys.odcivarchar2list
is
result_rows sys.odcivarchar2list;
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;
which also works for both SQL and PL/SQL (db<>fiddle).
Or you could use a pipelined function, with your PL/SQL collection type:
function unpack_list(
string_in in varchar2
) return result_str pipelined
is
begin
for r in (
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)
loop
pipe row (r.str);
end loop;
RETURN;
end;
which works in SQL, or in SQL running within PL/SQL, but not with direct assignment to a collection variable (db<>fiddle).
Which approach you take depends on how you need to call the function really. there may be some performance differences, but you might not notice unless they are called repeatedly and intensively.
