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

Question

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 ));

MT0 · Accepted Answer · 2025-06-09 12:10:45Z

2

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

answered Jun 9 at 12:10

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dsp_user Jun 9 at 12:39

thanks. it's kind of surprising that Oracle doesn't have a built-in solution for this. Anyway, I may end up using yours.

MT0 Jun 9 at 12:51

There is a built-in constructor arr2d tt_v2_255_arr_arr := tt_v2_255_arr_arr( tt_v2_255_arr('A', 'B'), tt_v2_255_arr('C', 'D') ); that takes static values but there is not built-in dynamic constructor because it does not appear to be a very useful thing to have when you can very simply do what you want with PL/SQL.

dsp_user Jun 9 at 12:54

I think it can be useful sometimes, for instance we have a bunch of 2d plsql tables (associative arrays) that we need to expose to a Java web application. For me, it would be best if Oracle 2d tables worked exactly the same as their plsql counterparts because that would mean less work. But OK, it is what it is.

Jon Heller · Accepted Answer · 2025-06-10 05:40:06Z

1

Since Oracle 21C, the new qualified expression syntax can simplify creating multidimensional arrays.

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
  BEGIN
    RETURN tt_v2_255_arr_arr(FOR i IN 1 .. num_times SEQUENCE => tt_v2_255_arr(value(1), value(2)));
  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 also outputs:

A
B
A
B

fiddle

answered Jun 10 at 5:40

Jon Heller

37k6 gold badges84 silver badges150 bronze badges

3 Comments

dsp_user Jun 10 at 9:42

thanks, Jon, we're not on 21C yet but this version does eliminate some of that unnecessary boilerplate code.

William Robertson Jun 21 at 11:17

Qualified expressions have been available since 18c.

Jon Heller Jun 21 at 14:27

Unfortunately the FOR ... SEQUENCE part of the syntax was not introduced until 21c. Without that part, qualified expressions don't really help here.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related