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.