oracle-databaseplsql

How to initialize 2D Oracle tables without having to use a constructor


The following code works but it seems that I have to initialize a 2D table (arr2D) using a constructor. In this case I'm passing 2 arr1D objects to the constructor but what if I don't know the exact number of items ahead of time. The types should be defined at a schema level ( I need to work with sql rather than plsql types)

CREATE OR REPLACE TYPE tt_v2_255_arr IS TABLE OF VARCHAR2(255);

CREATE OR REPLACE TYPE tt_v2_255_arr_arr IS TABLE OF tt_v2_255_arr;

declare

arr2d tt_v2_255_arr_arr;
arr1d tt_v2_255_arr  := tt_v2_255_arr();

begin

arr1d.extend(2);

arr2d :=  tt_v2_255_arr_arr( arr1d, arr1d );

arr2d(1)(1) := 'A';
arr2d(1)(2) := 'B';
arr2d(2)(1) := '1';
arr2d(2)(2) := '2';
dbms_output.put_line( arr2d(1)(2) );
exception when others then

 dbms_output.put_line( sqlerrm );

end;
/

So, how to initialize arr2D with an arbitrary numbers of objects?

For example, INIT function is supposed to create NUM_TIMES instances of arr1D and initialize arr2D with it.

arr2d :=  tt_v2_255_arr_arr( INIT(arr1d, NUM_TIMES ));

Solution

  • Create a function that creates the array-of-arrays, extends it to the correct length and then loops to create the sub-arrays at each index and copies the contents of the input array to the new location:

    DECLARE
      arr2d tt_v2_255_arr_arr;
      arr1d tt_v2_255_arr  := tt_v2_255_arr('A', 'B');
    
      FUNCTION INIT(
        value     tt_v2_255_arr,
        NUM_TIMES PLS_INTEGER
      ) RETURN tt_v2_255_arr_arr
      IS
        v_arr tt_v2_255_arr_arr := tt_v2_255_arr_arr();
      BEGIN
        v_arr.EXTEND(num_times);
        FOR i IN 1 .. NUM_TIMES LOOP
          v_arr(i) := tt_v2_255_arr();
          v_arr(i).EXTEND(value.COUNT);
          FOR j IN 1 .. value.COUNT LOOP
            v_arr(i)(j) := value(j);
          END LOOP;
        END LOOP;
        RETURN v_arr;
      END;
    BEGIN
      arr2d :=  INIT(arr1d, 2);
    
      FOR i IN 1..2 LOOP
        FOR j IN 1..2 LOOP
          dbms_output.put_line(arr2d(i)(j));
        END LOOP;
      END LOOP;
    END;
    /
    

    Which outputs:

    A
    B
    A
    B
    

    fiddle