Oracle 12c / Querying from an associative array

To use an array in SQL queries, it was previously necessary to create a “stored” type on the SQL server side and assign the appropriate rights to the user. Oracle 12c significantly simplifies working with arrays; queries can be made from an associative array declared in a package.

CREATE OR REPLACE PACKAGE MyPackage
AS
  -- associative array of strings
  TYPE Varchar_TT IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;

END;
/

DECLARE
  lt_Array MyPackage.Varchar_TT;
  l_Count PLS_INTEGER;
BEGIN
  lt_Array(1):='one';
  lt_Array(2):='two';
  
  SELECT COUNT(1)
    INTO l_Count
    FROM TABLE(lt_Array)
  ;
  
  dbms_output.put_line(l_Count); -- 2
END;

However, it’s necessary to take into account that such a construct unfortunately still cannot be used with DML operations.

CREATE TABLE my_table(
      text VARCHAR2(4000)
  );

DECLARE
  lt_Array MyPackage.Varchar_TT;
BEGIN
  lt_Array(1):='one';
  lt_Array(2):='two';
  
  INSERT INTO my_table
    SELECT *
      FROM TABLE(lt_Array) -- ORA-00902: invalid datatype
  ;
END;