Oracle equivalent for a Postgres function TEXT[] parameter, and ANY operator?-CodePudding

Am porting a Postgres PLPGSQL function to an Oracle stored procedure, but it has a TEXT[] array as a parameter, then uses this parameter in an ANY operation - specifically:

create or replace function func_name
(
    ...
    in_array_param text[],
    ...
) 
as $$
declare 
begin

    ...
    select column_1,
           column_2 
      from table_x           
     where (column_a = value)
     and (column_b = any(in_array_param));
    ...
end;

I don't recall Oracle historically having an equivalent to this (using a PL/SQL array in SQL), but with the evolution of PL/SQL, am wondering if there are now options to do this? Am using Oracle 19c, but can move to 21c if there is a newer construct that would help.

I have tried using a VARRAY, and using the IN operator:

   type my_array_type is varray(10) of varchar2(255);
   var_array my_array_type := my_array_type('1', '2', '3');

   procedure my_test(param_a varchar2, param_b my_array_type) is
       var_col test_tab.col2%type;
   begin
      select col2 into var_col
        from test_tab
      where col1 in param_b;
   end my_test;

   my_test('2', var_array);

This results in a PLS-00642 error:

ORA-06550: line 11, column 21:
PLS-00642: local collection types not allowed in SQL statements

CodePudding user response：

Have a look at the following example.

It uses built-in datatype (use it; why creating your own, if you don't have to?). Procedure searches through employees and finds the last (alphabetically) employee name in list of jobs passed as a collection.

I'd say that it is line #10 you're looking for.

SQL> create or replace procedure p_test
  2    (param_a in number, param_b in sys.odcivarchar2list)
  3  is
  4    l_ename emp.ename%type;
  5  begin
  6    select max(e.ename)
  7      into l_ename
  8      from emp e
  9      where e.deptno = param_a
 10        and e.job in (select * from table(param_b));
 11    dbms_output.put_line('Result: ' || l_ename);
 12  end;
 13  /

Procedure created.

Sample data:

SQL> select ename, job from emp where deptno = 20 order by job, ename;

ENAME      JOB
---------- ---------
FORD       ANALYST         --> this
SCOTT      ANALYST         --> this
ADAMS      CLERK
SMITH      CLERK
JONES      MANAGER         --> this

Who's the last among analysts and managers? These are Ford, Jones and Scott so - Scott it is:

SQL> set serveroutput on
SQL> exec p_test(param_a => 20, param_b => sys.odcivarchar2list('ANALYST', 'MANAGER'));
Result: SCOTT

PL/SQL procedure successfully completed.

SQL>