Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
/


dbms_output:
Col num 1 is "TD"
 Get col val 1

ORA-01007: variable not in select list
ORA-06512: at "SYS.DBMS_SQL", line 1795
ORA-06512: at line 21

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 || '"' );
    DBMS_SQL.DEFINE_COLUMN( l_cursor_id, idx, l_col_val, 4000);
  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;
/
1 rows affected

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

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 );
  FOR idx IN 1 .. l_num_cols LOOP
    DBMS_OUTPUT.PUT_LINE( 'Col num ' || idx || ' is "' || l_cols_desc(idx).col_name || '"' );
    DBMS_SQL.DEFINE_COLUMN( l_cursor_id, idx, l_col_val, 4000);
  END LOOP;
  l_status := DBMS_SQL.EXECUTE ( l_cursor_id );
  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;
/
1 rows affected

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