A recent comment of a reader about an obscure PL/SQL compiler error suggested me to begin writing a few postings about errors that you may come across when working with collections, so this is the first of a series, that i don't know yet how short or long will be, not counting the errors already described in previous articles.
Let's have a look at one of the most common ones as it is reported by SQLDeveloper:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 10
06531. 00000 - "Reference to uninitialized collection"
*Cause: An element or member function of a nested table or varray
was referenced (where an initialized collection is needed)
without the collection having been initialized.
*Action: Initialize the collection with an appropriate constructor
or whole-object assignment.
What does this mean?
It's easy to explain, let's take the following PL/SQL block:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type;
BEGIN
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 6
You cannot use the COUNT method without first initializing the collection
my_array.
Likewise, you cannot use the LIMIT method either, even if LIMIT refers to the upper bound that was specified in the declaration and theoretically has little to do with the actual varray content (see below for an example).
If you need to store the varray size in a variable for easier referencing for instance or you don't want to clutter the source with such literal values, you'll need to initialize the array first, as explained later on.
my_array has been declared of type
array_type, but it has not been
initialized and in this situation the collection is said to be
atomically NULL. Atomically null means that there are no items whatsoever in the collection that is why you cannot count them.
In order to initialize a collection you must use the
type constructor, that strange beast that is named after the collection type (
array_type in my example):
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type;
BEGIN
my_array := array_type();
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
Alternatively and according to a better programming practice, you can initialize the collection at declaration time:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type();
BEGIN
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
both programs will return the value 0 (zero) in the dbms_output buffer.
So now we have a VARRAY with zero elements, but we declared it to hold up to 10 items.
Let's have a look at how to initialize this collection with a given number of elements.
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type('a','b');
BEGIN
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
In dbms_output you'll find now the number 2 because we initialized the varray with two elements:
a and
b.
Imagine however that you have a large number of elements, say 32000, clearly you cannot type all of them in the constructor, so, how do you proceed?
If you are tempted to initialize the last element of the collection, see the next posting, so that is not an option.
How do you fully initialize a 32000 elements varray?
Before adding anything else, let me just suggest to ask yourself whether this is really necessary.
If the answer is yes, then read on, otherwise try to implement some other algorithm that doesn't consume db's resources so savagely...
Are you absolutely sure that this tiny program will not end up being used by dozen of concurrent users?
All right, so here comes into play the EXTEND collection method that allows us to initialize the varray by the desired number of null elements:
DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200);
my_array array_type := array_type();
BEGIN
my_array.EXTEND(32000);
dbms_output.enable;
dbms_output.put_line(my_array.COUNT);
END;
Last note: interestingly enough, the
official documentation says that this form of EXTEND cannot be used when you impose a NOT NULL constraint on the array (or table) type, but, at least on Oracle XE, this is not true:
DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200) NOT NULL;
my_array array_type := array_type();
BEGIN
dbms_output.enable;
my_array.EXTEND(31000);
dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null!'));
END;
Indeed if you try to initialize the array using a non empty constructor containing nulls, Oracle will complain at parse time:
DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200) NOT NULL;
my_array array_type := array_type(null,null);
BEGIN
dbms_output.enable;
my_array.EXTEND(31000);
dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null'));
END;
Error report:
ORA-06550: line 3, column 42:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
So, either i got it wrong or this is a bug...
Last but not least, let's peek at the most sensible and probably useful way of initializing a collection that is by using bulk SQL:
DECLARE
TYPE array_type IS VARRAY(32000) OF VARCHAR2(200);
my_array array_type := array_type();
upper_bound pls_integer := my_array.LIMIT;
BEGIN
dbms_output.enable;
select 'item_' || n
bulk collect into my_array
from (
select level n
from dual
connect by level <= upper_bound);
dbms_output.put_line(my_array.COUNT);
dbms_output.put_line(nvl(my_array(31000),'31000th is null'));
END;
Please note that i had to explicitly
initialize the array because i used
LIMIT for retrieving the array upper bound as i don't wanted to hardcode the literal
32000 inside the query, but if you don't use this kind of approach, you can omit the array initialization, it will be performed automatically when BULK COLLECT is performed.
------------------------------------------------
ORA-06531: Riferimento a collection non inizializzata
ORA-06531: Referencia a una recopilación no inicializada
ORA-06531: Referència a recollida no inicialitzada
ORA-06531: Référence à un ensemble non initialisé
ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06531: Αναφορά σε μη αρχικοποιημένη συλλογή
ORA-06531: Reference til ikke-initialiseret samling
ORA-06531: Referens till ej initierad insamling
ORA-06531: Referanse til uinitialisert samling
ORA-06531: Viittaus alustamattomaan kokoelma
ORA-06531: Inicializálatlan gyűjtőre való hivatkozás
ORA-06531: Referinţă la o colecţie neiniţializată
ORA-06531: Verwijzing naar niet-geïnitialiseerde verzameling.
ORA-06531: Referência para coleta não-inicializada
ORA-06531: Referência a uma recolha não inicializada
ORA-06531: Ссылка на неинициализированный набор
ORA-06531: Odkaz na neinicializovanou skupinu
ORA-06531: Odkaz na neiniciovanú kolekciu
ORA-06531: Odwołanie do nie zainicjowanej kolekcji
ORA-06531: Başlatılmamış koleksiyona başvuru
ORA-06531: Reference to uninitialized collection
See message translations for ORA-06531 and search additional resources
