PLS-00225 subprogram or cursor ” reference is out of scope when try to read column names

I’m trying to return the column names and the value as a pair key/value like this:

column_name | column_value
dep_id | 1
dep_name | ‘Test’
dep_create_date | ’08-25-2021′

declare
cursor c_dept is select * from department
begin
  for r_d in c_dept loop
  
    for c in c_dept."COLUMNS" loop
      dbms_output.put_line('Column name '|| c.name);
      dbms_output.put_line('Column value '|| c.value);
    end loop;
    
  end loop;
end;

When I execute this block, I got the following error message:

Error report – ORA-06550: line 6, column 22: PLS-00225: subprogram or cursor ‘C_DEPT’ reference is out of scope ORA-06550: line 6, column 5:

What I’m doing wrong?

Answer

Use the DBMS_SQL package:

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);
END;
/

Which outputs:

Column_name: DEP_ID
Column_value: 1
Column_name: DEP_NAME
Column_value: Test

db<>fiddle here