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?

CodePudding user response：

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.

CodePudding user response：

If that's how you're calling the function, then you're doing it wrong. Should be

SQL> CREATE TYPE result_str IS TABLE OF VARCHAR2 (100);
  2  /

Type created.

SQL> CREATE OR REPLACE FUNCTION unpack_list (string_in IN VARCHAR2)
  2     RETURN result_str
  3  IS
  4     result_rows  result_str;
  5  BEGIN
  6     WITH
  7        temp_table
  8        AS
  9           (    SELECT DISTINCT TRIM (REGEXP_SUBSTR (string_in,
 10                                                     '[^,] ',
 11                                                     1,
 12                                                     LEVEL)) str
 13                  FROM (SELECT string_in FROM DUAL) t
 14            CONNECT BY INSTR (string_in,
 15                              ',',
 16                              1,
 17                              LEVEL - 1) > 0)
 18     SELECT str
 19       BULK COLLECT INTO result_rows
 20       FROM temp_table;
 21
 22     RETURN result_rows;
 23  END;
 24  /

Function created.

Testing:

SQL> SELECT * FROM TABLE (unpack_list ('one1, two2'));

COLUMN_VALUE
--------------------------------------------------------------------------------
one1
two2

SQL>