Monday, December 01, 2008

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.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles