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
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.