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:
x
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
Advertisement
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