Two dimensional Varray

Question

How to create a 2D varray in Oracle??

It can be a string array or an integer or both.

Answer 1

You cannot create a 2-dimensional VARRAY. You can either:

1. Create a VARRAY containing a VARRAY:

   CREATE TYPE numbers_column AS VARRAY(3) OF NUMBER;
   CREATE TYPE numbers_matrix AS VARRAY(3) OF numbers_column;
   
   SELECT numbers_matrix(
            numbers_column(1,4,7),
            numbers_column(2,5,8),
            numbers_column(3,6,9),
          )
   FROM   DUAL;
   

2. Use a 1-dimensional VARRAY containing MxN items and when you want to get the item at position (x,y) then use arithmetic to calculate the position and get the x+(y-1)*M^th element:

   CREATE TYPE numbers_matrix AS VARRAY(9) OF NUMBER;
   
   SELECT numbers_matrix(
            1,2,3,
            4,5,6,
            7,8,9
          )
   FROM   DUAL;