Two dimensional array in PL/SQL

Two dimensional array in PL/SQL

Type CAR_TABLE_ARRAY is varray(2) of varchar2(255);
TYPE CAR_TABLE_TYPE IS TABLE OF CAR_TABLE_ARRAY;
CAR_TABLE CAR_TABLE_TYPE;

CAR_TABLE := CAR_TABLE_TYPE();
CAR_TABLE.EXTEND(10);
CAR_TABLE(1)(1) := 'DODGE';
CAR_TABLE(2)(1) := 'FORD';
CAR_TABLE(3)(1) := 'MUSTANG';
CAR_TABLE(4)(1) := 'EDSEL';
CAR_TABLE(5)(1) := 'STUDEBAKER';

DBMS_OUTPUT.put_line( '1 ' || CAR_TABLE(1)(1) );
DBMS_OUTPUT.put_line( '2 ' || CAR_TABLE(2)(1) );
DBMS_OUTPUT.put_line( '3 ' || CAR_TABLE(3)(1) );
DBMS_OUTPUT.put_line( '4 ' || CAR_TABLE(4)(1) );
DBMS_OUTPUT.put_line( '5 ' || CAR_TABLE(5)(1) )

When I run I get the following error:

ORA-06531: Reference to uninitialized collection

Here's an example of using an multidimensional array in pl/sql. Here I use an array containing an array.

declare



  type t_features is table of varchar(100) index by pls_integer;

  type t_car_rec is record

  (

    make varchar2(50),

    model varchar2(50),

    features t_features

  );



  type t_car_tab is table of t_car_rec index by pls_integer;

  car_tab t_car_tab;



  procedure show_detail is

    car_idx pls_integer;

    features_idx pls_integer;

  begin

    car_idx := car_tab.first;

    loop

      exit when car_idx is null;

      dbms_output.put_line('Details for ' || car_tab(car_idx).make || ' ' || car_tab(car_idx).model);



      features_idx := car_tab(car_idx).features.first;

      loop

        exit when features_idx is null;

        dbms_output.put_line('   =>' || car_tab(car_idx).features(features_idx));



        features_idx := car_tab(car_idx).features.next(features_idx);

      end loop;



      car_idx := car_tab.next(car_idx);

    end loop;

  end;



begin



  -- using sequential index values

  car_tab(1).make := 'Ferrari';

  car_tab(1).model := 'Testarossa';

  car_tab(1).features(1) := 'Fast';

  car_tab(1).features(2) := 'Looks cool';

  car_tab(1).features(3) := 'Expensive';



  -- using random index values (sparse)

  car_tab(2).make := 'Acura';

  car_tab(2).model := 'TSX';

  car_tab(2).features(14) := 'Small';

  car_tab(2).features(200) := 'Good MPG';

  car_tab(2).features(36) := 'Inexpensive';



  show_detail;



end;

Output would be:

Details for Ferrari Testarossa

  =>Fast

  =>Looks cool

  =>Expensive 

Details for Acura TSX

  =>Small

  =>Inexpensive

  =>Good MPG

Or

That's because you're referencing the inner arrays which are not initialized.
Either add something like:

CAR_TABLE(1) := CAR_TABLE_ARRAY();

CAR_TABLE(1).EXTEND(1);

CAR_TABLE(2) := CAR_TABLE_ARRAY();

CAR_TABLE(2).EXTEND(1);

...

Or make the inner arrays (CAR_TABLE_ARRAY) as asociative arrays:

Type CAR_TABLE_ARRAY is TABLE of varchar2(255) index by binary_integer;

Or

VARRAY and nested table user-defined datatypes always have to be initialized using a constructor. You're doing that correctly for the nested table, but not for the VARRAYs that it contains. The simplest fix is to call the constructor in the assignment lines:

declare

    Type CAR_TABLE_ARRAY is varray(2) of varchar2(255);

    TYPE CAR_TABLE_TYPE IS TABLE OF CAR_TABLE_ARRAY;

    CAR_TABLE CAR_TABLE_TYPE;

begin    

    CAR_TABLE := CAR_TABLE_TYPE();

    CAR_TABLE.EXTEND(10);

    CAR_TABLE(1) := CAR_TABLE_ARRAY('DODGE',null);

    CAR_TABLE(2) := CAR_TABLE_ARRAY('FORD',null);

    CAR_TABLE(3) := CAR_TABLE_ARRAY('MUSTANG',null);

    CAR_TABLE(4) := CAR_TABLE_ARRAY('EDSEL',null);

    CAR_TABLE(5) := CAR_TABLE_ARRAY('STUDEBAKER',null);



    DBMS_OUTPUT.put_line( '1 ' || CAR_TABLE(1)(1) );

    DBMS_OUTPUT.put_line( '2 ' || CAR_TABLE(2)(1) );

    DBMS_OUTPUT.put_line( '3 ' || CAR_TABLE(3)(1) );

    DBMS_OUTPUT.put_line( '4 ' || CAR_TABLE(4)(1) );

    DBMS_OUTPUT.put_line( '5 ' || CAR_TABLE(5)(1) );

end;

Is is possible to create two dimensional arrays in PL/SQL? I have tried several strategies to no avail. The last thing I've tried is creating object type of varray's like so:

CREATE OR REPLACE TYPE
tSQL_COL
IS
VARRAY(200)
OF
VARCHAR2(1000);

CREATE OR REPLACE TYPE
tSQL_SET
IS
object
(
SELECT_ITEM tSQL_CO := tSQL_COL(),
FROM_ITEM tSQL_CO := tSQL_COL(),
WHERE_ITEM tSQL_CO := tSQL_COL(),
ORDER_ITEM tSQL_CO := tSQL_COL(),
);

Then in a package I declare:

TYPE SQL_ITEM_SET IS VARRAY(200) OF tSQL_SET;

However the tSQL_SET type is always invalid. Is there another way to create a 2x array or something else I'm missing?

TIA

__________________
Bob, Eater of Squid