Oracle DBMS_SQL giving ORA-1007 variable not in select list

Question

I get this on Oracle 11g and 19c.

Below is a simplified example of the code I am using to process some queries dynamically. It needs to be DBMS_SQL because the actual use case will have various queries generated all with differing column names and types.

The code:

SET SERVEROUTPUT ON
DECLARE
  l_qry VARCHAR2(1000) := 'SELECT TO_CHAR(SYSDATE) td FROM DUAL';
  l_cursor_id  INTEGER;
  l_col_val    VARCHAR2(4000);
  l_status     INTEGER;
  l_num_cols   NUMBER := 0;
  l_cols_desc  DBMS_SQL.DESC_TAB;
BEGIN
  l_cursor_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE( l_cursor_id, l_qry, DBMS_SQL.NATIVE );
  DBMS_SQL.DESCRIBE_COLUMNS( l_cursor_id, l_num_cols, l_cols_desc );
  l_status := DBMS_SQL.EXECUTE ( l_cursor_id );
  FOR idx IN 1 .. l_num_cols LOOP
    DBMS_OUTPUT.PUT_LINE( 'Col num ' || idx || ' is "' || l_cols_desc(idx).col_name || '"' );
  END LOOP;
  LOOP
    l_status := DBMS_SQL.FETCH_ROWS( l_cursor_id );
    EXIT WHEN l_status <= 0;
    FOR idx IN 1 .. l_num_cols  LOOP
      DBMS_OUTPUT.PUT_LINE( ' Get col val ' || idx );
      DBMS_SQL.COLUMN_VALUE( l_cursor_id, idx, l_col_val );
      DBMS_OUTPUT.PUT_LINE( ' Value: ' || l_col_val );
    END LOOP;
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR( l_cursor_id );
END;
/

The result when I run this is:

Col num 1 is "TD"
Get col val 1
DECLARE
*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "SYS.DBMS_SQL", line 1854
ORA-06512: at line 22

Answer 1

You missed a step from the operational notes; you haven't called the DEFINE_COLUMN procedure in your first loop.

...
BEGIN
  l_cursor_id := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE( l_cursor_id, l_qry, DBMS_SQL.NATIVE );
  DBMS_SQL.DESCRIBE_COLUMNS( l_cursor_id, l_num_cols, l_cols_desc );
  l_status := DBMS_SQL.EXECUTE ( l_cursor_id );
  FOR idx IN 1 .. l_num_cols LOOP
    DBMS_OUTPUT.PUT_LINE( 'Col num ' || idx || ' is "' || l_cols_desc(idx).col_name || '"' );
    -- next line was missing
    DBMS_SQL.DEFINE_COLUMN( l_cursor_id, idx, l_col_val, 4000);
  END LOOP;
...

With that change it works and gives output:

dbms_output:
Col num 1 is "TD"
 Get col val 1
 Value: 14-NOV-24

The documentation says to do that before EXECUTE, but it seems to work both ways.

fiddle