PL/SQL Initiate a multi-dimensional array INDEX BY VARCHAR2

Question

I have the following code which works just fine in building a 2D array indexed by VARCHAR2. But I have a question about how I would instantiate the top-level array with an "empty" second level array. (See the comments in the code.)

The only way I've found to instantiate the initial array (inside the Add() procedure) is to declare a dummy variable (which is itself an empty array), and assign it to the top-level array:

DECLARE

   TYPE pt_array  IS TABLE OF number   INDEX BY VARCHAR2(25);
   TYPE prj_array IS TABLE OF pt_array INDEX BY VARCHAR2(25);

   pt_array_ pt_array;
   pj_array_ prj_array;

   PROCEDURE Add (
      act_  IN VARCHAR2,
      pt_   IN VARCHAR2,
      val_  IN NUMBER )
   IS
      dummy_empty_array pt_array; -- initiate a dummy variable
   BEGIN
      IF not pj_array_.EXISTS(act_) THEN
         pj_array_(act_) := dummy_empty_array; -- can I initiate here without having to declare a variable?
      END IF;
      IF not pj_array_(act_).EXISTS(pt_) THEN
         pj_array_(act_)(pt_) := val_;
      END IF;
   END Add;

BEGIN
   Add ('A', '123', 1);
   Add ('A', '456', 1);
   Add ('B', '456', 1);
END;

Most languages offer the ability to assign an anonymous array when required (i.e. without having to define an intermittent variable). For example, in Javascript you'd just do x = [];. But I haven't found a way to do this in PL/SQL.

I've tried various different syntaxes, one obvious example being the following (which errors at compile time with "no function with name 'PT_ARRAY' exists"):

    pj_array_(act_) := pt_array();

Also, the concept of using keyword EXTEND doesn't apply if you're declaring you array/tables with an INDEX BY clause.

So my question is whether it's possible to write this code (i.e. to initiate the empty array) without the dummy variable?

Answer 1

Support for the syntax you want (pj_array_(act_) := pt_array();) was added in Oracle Database 18c:

DECLARE

   TYPE pt_array  IS TABLE OF number   INDEX BY VARCHAR2(25);
   TYPE prj_array IS TABLE OF pt_array INDEX BY VARCHAR2(25);

   pt_array_ pt_array;
   pj_array_ prj_array;

   PROCEDURE Add (
      act_  IN VARCHAR2,
      pt_   IN VARCHAR2,
      val_  IN NUMBER )
   IS
   BEGIN
      IF not pj_array_.EXISTS(act_) THEN
         pj_array_(act_) := pt_array();
      END IF;
      IF not pj_array_(act_).EXISTS(pt_) THEN
         pj_array_(act_)(pt_) := val_;
      END IF;
   END Add;

BEGIN
   Add ('A', '123', 1);
   Add ('A', '456', 2);
   Add ('B', '456', 3);

   dbms_output.put_line ( pj_array_ ('A')('123') );
   dbms_output.put_line ( pj_array_ ('A')('456') );
   dbms_output.put_line ( pj_array_ ('B')('456') );
END;
/
1
2
3