Oracle SQL function - wrong data type in function call

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.