You can get the following error:
ORA-01007: variable not in select listwhen 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):
declareWhen i executed the program, i got ORA-01007 because the remote table had a missing column.
type prf_runs_type is table of plsql_profiler_runs%rowtype;
ORDER BY runid'
bulk collect into rmt_runs_tab;
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:
- manually define the record type, specifying the desired columns explicitly;
- define a view containing the columns in the desired sequence and use %ROWTYPE to implicitly create a RECORD type based on the view definition.
See message translations for ORA-01007 and search additional resources.