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