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?
CodePudding user response:
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