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
