ORA-01007

Recently a colleague ask me about how to analyze the following short dump:

 Database error text........: "ORA-01007: variable not in select list"
\
 Database error code........: 1007
\
 Triggering SQL statement...: "FETCH NEXT "
\
 Internal call code.........: "[DBDS/NEW DSQL]"
\

\

The error was raised within a standard SAP report with the following statement:

>>>>>       select tablespace_name,
\
 1435          initial_extent / 1024,
\
 1436          next_extent / 1024,
\
 1437          min_extents,
\
 1438          max_extents,
\
 1439          pct_increase,
\
 1440          pct_free,
\
 1441          pct_used,
\
 1442          ini_trans,
\
 1443          freelists,
\
 1444          freelist_groups,
\
 1445          iot_type,
\
 1446          partitioned
\
 1447       from user_tables into :ora_para
\
 1448       where table_name = :tabname
\
 1449     ENDEXEC.
\
 1450   endif.
\

Does not look too suspicious, so the next thing to look for is whether the table queried would fit to the SQL statement.
The USER_TABLES view is again a standard view for Oracle databases and looks like this in 10g (for easy comparison I marked the columns used in our statement with a '->')

desc user_tables
\
      Name                         Null?    Type
\
      ---------------------------- -------- ---------------
\
   -> TABLE_NAME                   NOT NULL VARCHAR2(30)
\
   -> TABLESPACE_NAME                       VARCHAR2(30)
\
      CLUSTER_NAME                          VARCHAR2(30)
\
      IOT_NAME                              VARCHAR2(30)
\
      STATUS                                VARCHAR2(8)
\
   -> PCT_FREE                              NUMBER
\
   -> PCT_USED                              NUMBER
\
   -> INI_TRANS                             NUMBER
\
      MAX_TRANS                             NUMBER
\
   -> INITIAL_EXTENT                        NUMBER
\
   -> NEXT_EXTENT                           NUMBER
\
   -> MIN_EXTENTS                           NUMBER
\
   -> MAX_EXTENTS                           NUMBER
\
      PCT_INCREASE                          NUMBER
\
   -> FREELISTS                             NUMBER
\
   -> FREELIST_GROUPS                       NUMBER
\
      LOGGING                               VARCHAR2(3)
\
      BACKED_UP                             VARCHAR2(1)
\
      NUM_ROWS                              NUMBER
\
      BLOCKS                                NUMBER
\
      EMPTY_BLOCKS                          NUMBER
\
      AVG_SPACE                             NUMBER
\
      CHAIN_CNT                             NUMBER
\
      AVG_ROW_LEN                           NUMBER
\
      AVG_SPACE_FREELIST_BLOCKS             NUMBER
\
      NUM_FREELIST_BLOCKS                   NUMBER
\
      DEGREE                                VARCHAR2(10)
\
      INSTANCES                             VARCHAR2(10)
\
      CACHE                                 VARCHAR2(5)
\
      TABLE_LOCK                            VARCHAR2(8)
\
      SAMPLE_SIZE                           NUMBER
\
      LAST_ANALYZED                         DATE
\
   -> PARTITIONED                           VARCHAR2(3)
\
   -> IOT_TYPE                              VARCHAR2(12)
\
      TEMPORARY                             VARCHAR2(1)
\
      SECONDARY                             VARCHAR2(1)
\
      NESTED                                VARCHAR2(3)
\
      BUFFER_POOL                           VARCHAR2(7)
\
      ROW_MOVEMENT                          VARCHAR2(8)
\
      GLOBAL_STATS                          VARCHAR2(3)
\
      USER_STATS                            VARCHAR2(3)
\
      DURATION                              VARCHAR2(15)
\
      SKIP_CORRUPT                          VARCHAR2(8)
\
      MONITORING                            VARCHAR2(3)
\
      CLUSTER_OWNER                         VARCHAR2(30)
\
      DEPENDENCIES                          VARCHAR2(8)
\
      COMPRESSION                           VARCHAR2(8)
\
      DROPPED                               VARCHAR2(3)
\

Still nothing too suspicious here - all columns used in our statement are present in the view.
Maybe explaining the statement will shed some light to this issue?

 
\
explain plan for
\
select tablespace_name, initial_extent / 1024, next_extent / 1024,
\
min_extents, max_extents, pct_increase, pct_free, pct_used, ini_trans,
\
freelists, freelist_groups, iot_type, partitioned
\
from user_tables
\
where table_name = :A0 ;
\

\
Explained.
\

\
select * from table(dbms_xplan.display);
\

\

\
PLAN_TABLE_OUTPUT
\
----------------------------------------------------------------------------------
\
Plan hash value: 19987672
\

\
-----------------------------------------------------------------------------
\
| Id  | Operation                          | Name           | Rows  | Cost  |
\
-----------------------------------------------------------------------------
\
|   0 | SELECT STATEMENT                   |                |     1 |     3 |
\
|   1 |  NESTED LOOPS OUTER                |                |     1 |     2 |
\
|   2 |   NESTED LOOPS OUTER               |                |     1 |     2 |
\
|   3 |    NESTED LOOPS OUTER              |                |     1 |     2 |
\
|   4 |     NESTED LOOPS OUTER             |                |     1 |     2 |
\
|   5 |      NESTED LOOPS                  |                |     1 |     2 |
\
|   6 |       NESTED LOOPS                 |                |     1 |     1 |
\
|   7 |        NESTED LOOPS                |                |     1 |     1 |
\
|*  8 |         HASH JOIN                  |                |     1 |     1 |
\
|*  9 |          FIXED TABLE FULL          | X$KSPPI        |     1 |     0 |
\
|  10 |          FIXED TABLE FULL          | X$KSPPCV       |  1491 |     0 |
\
|* 11 |         TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |     0 |
\
|* 12 |          INDEX RANGE SCAN          | I_OBJ2         |     1 |     0 |
\
|* 13 |        TABLE ACCESS CLUSTER        | TAB$           |     1 |     0 |
\
|* 14 |         INDEX UNIQUE SCAN          | I_OBJ#         |     1 |     0 |
\
|  15 |       TABLE ACCESS CLUSTER         | TS$            |     1 |     0 |
\
|* 16 |        INDEX UNIQUE SCAN           | I_TS#          |     1 |     0 |
\
|  17 |      TABLE ACCESS CLUSTER          | SEG$           |     1 |     0 |
\
|* 18 |       INDEX UNIQUE SCAN            | I_FILE#_BLOCK# |     1 |     0 |
\
|* 19 |     INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     0 |
\
|  20 |    TABLE ACCESS BY INDEX ROWID     | OBJ$           |     1 |     0 |
\
|* 21 |     INDEX UNIQUE SCAN              | I_OBJ1         |     1 |     0 |
\
|  22 |   TABLE ACCESS CLUSTER             | USER$          |     1 |     0 |
\
|* 23 |    INDEX UNIQUE SCAN               | I_USER#        |     1 |     0 |
\
-----------------------------------------------------------------------------
\

Also the EXPLAIN works, so what is this about?
The first important hint is already present in the short dump:

   Triggering SQL statement...: "FETCH NEXT "

This tells us that the error did not occur during parsing or opening (that is executing) the query.
Instead the error occured when our workprocess, the client for the database, tried to fetch the result set from the server process to the client process.

In fact, this is already the major part in understanding this issue.
The error message "ORA-01007: variable not in select list" tells us that the client wanted to fetch a value from the server that was not selected in the statement before.
Now how can that be?
We've seen that all columns from the SQL statement were present in the table!

There's one thing we didn't pay attention to - until now:

>>>>>       select tablespace_name,
\
 1435          initial_extent / 1024,
\
   [...]
\
 1447       from user_tables into :ora_para              <<<<
\
 1448       where table_name = :tabname

This is not just a simple SELECT statement, but a SELECT ... INTO statement.
With the INTO clause we tell the Oracle client to accept whatever we give it as the memory structure to put the result data into.

Knowing that, it's time to check what exactly we gave the client here.
The workspace variable :ora_para is defined in the DATA section of the ABAP report.
Using transaction ST22 (where we already checked the short dump itself) we simply use the "ABAP EDITOR" button to navigate to the source code and surely enough we find the definition of :ora_para in the DATA section:

  data: ora_para like oratabsti,
\
        db_index like dd12l-dbindex.

Our ora_para is defined to look like a table or structure named ORATABSTI.
By double clicking on the name in the ABAP editor or by using transaction SE11 we can check the layout of this DDIC object.

ORATABSTI looks like this:
\

\
  Fields of active runtime object
\
  -------------------------
\
  | Position |Field Name  |
\
  -------------------------
\
  |   1      |TABSPACE    |
\
  |   2      |INIT        |
\
  |   3      |NEXT        |
\
  |   4      |MINEXT      |
\
  |   5      |MAXEXT      |
\
  |   6      |PCTINC      |
\
  |   7      |OPCTFREE    |
\
  |   8      |OPCTUSED    |
\
  |   9      |INITRANS    |
\
  |  10      |OFREELIST   |
\
  |  11      |OFREEGROUP  |
\
  |  12      |INDEXORG    |
\
  |  13      |PARTY       |
\
  |  14      |COMPRESSION |
\
  |  15      |COMPRESS_FOR|
\
  -------------------------

Do you spot it?
There are two additional fields in this structure!

  |  14      |COMPRESSION |
\
  |  15      |COMPRESS_FOR|

These columns don't occur in our statement, although they are present in the USER_TABLES view.

Now it's pretty clear what happened:
The SAP workprocess sucessfully prepared and executed the SELECT statement.
When it came to fetch the data from the Oracle server it told the Oracle client software to get the values for column TABSPACE, for column INIT, for column NEXT etc.
This went without problems until the Oracle client tried to get the values for column COMPRESSION.
Since this column was not part of the result set, the Oracle client software correctly raised the error:

"ORA-01007: variable not in select list"

As usual, once you know the cause of a problem, resolving it is most often not too difficult.
By either changing the ORATABASTI or the report the problem can easily be solved.

Hope this error is not that confusing anymore from now on :-)

regards,
Lars