PLS-00225 子程序或游标引用在尝试读取列名时超出范围

Question

我正试图将列名和值作为一对键/值返回，如下所示：

column_name|column_value
dep_id|1
dep_name|'测试'
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;

当我执行这个块时，我得到了以下错误消息：

错误报告-ORA-06550:第6行，第22列：PLS-00225:子程序或光标"C_DEPT"引用超出范围ORA-06550:第6行，第5列：

我做错了什么？

Answer 1

使用DBMS_SQL包：

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

哪个输出：

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

db<gt；小提琴这里