ORA-01007: variable not in select list

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You can get the following error:

ORA-01007: variable not in select list

when you are trying to perform a SELECT... BULK COLLECT INTO using a collection of RECORD data type and the column list in the query differs from the column list of the RECORD data type.
For instance, i was executing a program containing the following code, where the RECORD definition (implicitly created with %rowtype) is based on the structure of a local table but the data is retrieved from a remote table via a db link and the two structures are supposedly identical (but they are not):

declare
type prf_runs_type     is table of plsql_profiler_runs%rowtype;
rmt_runs_tab           prf_runs_type;
...
begin
...
execute immediate
'SELECT *
FROM  plsql_profiler_runs@yy_rmt_link
ORDER BY runid'
  bulk collect into rmt_runs_tab;
...
end;

When i executed the program, i got ORA-01007 because the remote table had a missing column.
Indeed i forgot to update the remote table definition and add the same column i have in my local database.
Note also that implicit columns defined with statements like SELECT * ... are a typical source of problems because exact column positioning becomes a critical factor. If instead we specify a fixed list of columns, the column positioning issue will be ruled out. A mismatching columns order will easily lead to ORA-01858.

Likewise, we might want to define a RECORD structure that is a subset of the original columns.
In this case we have two options:

1. manually define the record type, specifying the desired columns explicitly;
2. define a view containing the columns in the desired sequence and use %ROWTYPE to implicitly create a RECORD type based on the view definition.

I prefer the latter approach because theoretically it makes possible to shrink or enlarge the column list without touching the program, under certain conditions.

See message translations for ORA-01007 and search additional resources.

ORA-06531: Reference to uninitialized collection

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

Update ... returning into misunderstandings

I don't know why, but for some time i have been living in the belief that the following statement would end up in an ORA-01403 no data found error:

update some_table
set some_column = some_value
where 1 = 0
returning some_other_column into some_variable;

Probably this is due to the fact that the documentation, up to date, doesn't cover this specific situation (correct me if i am wrong) and much is left to the imagination of the programmer.

Hence, if you are still convinced that the sql statement above should cause a run time exception, as it would happen with

select some_column
into some_variable
from some_table
where 1 = 0;

ORA-01403: no data found

well, you'd be better off checking your programs right now, especially if you work in a nuclear plant or at some missile shield project :-)

As perhaps Oscar Wilde would say if he had had a chance to be a PL/SQL programmer, "there is only one thing in the world worse than a program raising an unexpected exception, and that is a program not raising an expected exception."

Indeed, the update will run smoothly and guess what, some_variable will not be updated at all, that means, it will retain its previous value, if any.

In other words, if some_variable's value prior to executing the update was 'X', it will still be 'X' after executing the update.
This means that if you want to be sure that this variable holds a consistent value after the update, whatever the outcome of update will be, you should take no chances and reset the variable yourself, before executing that statement.

some_variable := null;
update ...
returning ... into...;

Interestingly enough, if the where condition in the update doesn't result in a unique fetch, you'll get:

ORA-01422: exact fetch returns more than
requested number of rows

If you trap this error and inspect the content of some_variable, you'll see it contains the value of the column retrieved from the first matching record.
And i guess that in certain situations this could be a useful feature.

If you need to handle multiple rows in one shot, remember that you can use the UPDATE ... BULK COLLECT INTO syntax:

declare
type my_array_type is
table of some_table.some_column%type
index by binary_integer;
my_array  my_array_type;
begin
...
update some_table
set some_column = some_value
where ...
returning some_expression bulk collect into my_array;

end;

No nukes, please!

PLS-00315 and PLS-00657

Have you ever tried to create an associative array with index by other than binary_integer?

In Oracle 8i, if i remember well, binary_integer was the only option available.

Starting from 9i, you could define a varchar2 indexed array, making oracle arrays very similar to the associative arrays found in other programming languages.
However there are still some limitations, for instance you cannot create a collection based on a date index and a declaration like the following would return a compilation error:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR INDEX BY DATE;
current_year_tab  bank_holiday_tab_type;
BEGIN
...
END;
PLS-00315 Implementation restriction:
unsupported table index type.

But not all is lost.
An index-by-date array could be easily simulated by converting the date value into a proper varchar2 type value using function TO_CHAR with the desired date format mask.

The drawback when using such associative arrays, is in that you cannot use BULK COLLECT or FORALL constructs, because they work only with numerical integer indices.

In case you attempt to use one of such statements, you'll get an exception at compile-time like the following:

PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported.

The reason is quite simple, it's impossible for Oracle to predict what the next VARCHAR2 subscript should be, whereas in the case of a binary integer subscript it is just automatically filling up the array with consecutive numbers, starting from 1.

In other words a normal array is something like:
A(1), A(2),... A(n)

Where the value of each element can be any of any type you like, including a user defined object type.

On the contrary an associative array is something like:

A(string₁), A(string₂),... A(string_n)

If you look at the syntax of BULK COLLECT, you can see that there is no way to specify a mapping between the value being returned from the table and its corresponding index.

For instance, let's take my original array definition and transform it into a varchar2(8) indexed array.
Suppose we need to fill up the aforementioned array of dates, where each value is a string indicating the bank holiday name.
There is no way of doing this with BULK COLLECT.

CREATE TABLE bank_holidays(
 bank_holiday DATE,
 bank_holiday_name VARCHAR2(50));

DECLARE
TYPE bank_holiday_tab_type IS
  TABLE OF VARCHAR2(50)
  INDEX BY VARCHAR2(8);
current_year_tab  bank_holiday_tab_type;
BEGIN
SELECT TO_CHAR(bank_holiday,'YYYYMMDD'),
     bank_holiday_name
BULK COLLECT INTO current_year_tab
FROM dates_of_year;
...
END;

As you see we would need to stuff a pair of table columns (the subscript and its value) as a single element of the array, but BULK COLLECT is not designed to do that.

For the sake of discussion, let's suppose it makes some sense to get rid of the column bank_holiday_name from the last sql statement above, just to see what happens when we try to compile this program in 10g.

Well, all we get is:

PLS-00657: Implementation restriction:
bulk SQL with associative arrays with
VARCHAR2 key is not supported

So, while we wait until oracle comes up with a statement syntax extension like:

SELECT BULK_PAIR(TO_CHAR(bank_holiday,'YYYYMMDD'), bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;

we can stick to a classic and slower explicit cursor doing the job:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(8);
current_year_tab  bank_holiday_tab_type;
BEGIN
FOR each_record IN (
SELECT TO_CHAR(bank_holiday,'YYYYMMDD') bank_holiday$,
   bank_holiday_name
FROM bank_holidays)
LOOP
current_year_tab(each_record.bank_holiday$)
:= each_record.bank_holiday_name;
END LOOP;
END;

or perhaps define a custom type like this:

CREATE TYPE bank_holiday_type AS
OBJECT (bank_holiday DATE, bank_holiday_name VARCHAR2(50));

and then use BULK COLLECT on an array of bank_holiday_type, as follows:

DECLARE
TYPE bank_holiday_tab_type IS
TABLE OF bank_holiday_type INDEX BY BINARY_INTEGER;
current_year_tab   bank_holiday_tab_type;
BEGIN

SELECT
bank_holiday_type(
  TRUNC(bank_holiday),
        bank_holiday_name)
BULK COLLECT INTO current_year_tab
FROM bank_holidays;
END;

Happy collecting.