By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE
v_cursor PLS_INTEGER;
v_cnt PLS_INTEGER;
v_desc dbms_sql.desc_tab;
i PLS_INTEGER;
v_ignore PLS_INTEGER;
v_value VARCHAR2(4000);
BEGIN
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(
v_cursor,
'select 1 as dep_id, ''Test'' as dep_name from dual',
dbms_sql.native
);
dbms_sql.describe_columns(v_cursor, v_cnt, v_desc);
i := v_desc.FIRST;
LOOP
EXIT WHEN i IS NULL;
dbms_sql.define_column(v_cursor, i, v_value, 4000);
i := v_desc.NEXT(i);
END LOOP;
v_ignore := dbms_sql.execute(v_cursor);
LOOP
EXIT WHEN dbms_sql.fetch_rows(v_cursor) <= 0;
i := v_desc.FIRST;
LOOP
EXIT WHEN i IS NULL;
dbms_sql.column_value(v_cursor, i, v_value);
dbms_output.put_line('Column_name: ' || v_desc(i).COL_NAME);
dbms_output.put_line('Column_value: ' || v_value);
i := v_desc.NEXT(i);
END LOOP;
END LOOP;
dbms_sql.close_cursor(v_cursor);
1 rows affected
dbms_output:
Column_name: DEP_ID
Column_value: 1
Column_name: DEP_NAME
Column_value: Test