Not able to create a matrix in PL/SQL using nested tables

I was trying to create a matrix in PL/SQL using nested tables as such:

• firstly, I create a table type that is able to store varchars (that in my case is name lista_principala)
• secondly, using the previously declare table type lista_principala I try to create a table type that contains items of type lista_principala. I don’t think that I’m wrong about my logic, but my PL/SQL implementation looks something like this:

set serveroutput on;

DECLARE
    TYPE lista_principala_tip IS TABLE OF varchar2(30);
    TYPE lista_liste_tip IS TABLE OF lista_principala_tip;
    lista lista_liste_tip;
BEGIN
    lista := lista_liste_tip();
    lista.extend(20);
    lista(1) := lista_principala_tip('Gorila', 'Babuin', 'Urangutan', 'Cimpanzeu', 'Gibon');
    lista(2) := lista_principala_tip('Labrador', 'Bulldog', 'Bichon', 'Ciobanesc German');
    lista(3) := lista_principala_tip('British Shorthair', 'Siamese', 'Scottish Fold', 'Chartreux');
    for i in lista.first..lista.last loop
        for j in lista(i).first..lista(i).last loop
            DBMS_OUTPUT.PUT_LINE(i||' - '||lista(i)(j));  
        end loop;
    end loop;
END;

And the problem that I have is that when I try to run this script I get the following errors:

Error report – ORA-06531: Reference to uninitialized collection ORA-06512: at line 12

Answer

You use lista.extend(20); to create a list with 20 items and these will all be initialised to NULL.

Then you set the values for the first 3 elements of the collection.

Then you loop through all 20 items and try to loop through the sub-list in each element; however, after the first 3, there is no sub-list contained in the element as the element is NULL.

Either:

• Just lista.EXTEND(3); as you only want 3 elements to the array; or
• Add a check if the list element is NULL and then skip looping through the sub-list if it is.

The second option can be implemented as:

DECLARE
    TYPE lista_principala_tip IS TABLE OF varchar2(30);
    TYPE lista_liste_tip IS TABLE OF lista_principala_tip;
    lista lista_liste_tip;
BEGIN
    lista := lista_liste_tip();
    lista.extend(20);
    lista(1) := lista_principala_tip('Gorila', 'Babuin', 'Urangutan', 'Cimpanzeu', 'Gibon');
    lista(2) := lista_principala_tip('Labrador', 'Bulldog', 'Bichon', 'Ciobanesc German');
    lista(3) := lista_principala_tip('British Shorthair', 'Siamese', 'Scottish Fold', 'Chartreux');
    for i in lista.first..lista.last loop
        IF lista(i) IS NOT NULL THEN
            for j in lista(i).first..lista(i).last loop
                DBMS_OUTPUT.PUT_LINE(i||' - '||lista(i)(j));  
            end loop;
        END IF;
    end loop;
END;
/

db<>fiddle here