Using XML transformation in a cursor in Oracle PL/SQL, we can fetch just one column of the recordset returned back by a SYS_REFCURSOR:
declare
the_sys_refcursor sys_Refcursor;
P_Return_Set_O sys_Refcursor;
p_column_name varchar2(500);
p_column_value varchar2(500);
CURSOR get_columns (the_column varchar2) IS
SELECT t2.column_value.getrootelement() name,
EXTRACTVALUE(t2.column_value, 'node()') value
FROM (SELECT * FROM TABLE (XMLSEQUENCE(P_Return_Set_O))) t1,
TABLE (XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2
where 1=1
and t2.column_value.getrootelement() = the_column;
--After cursor and variable declarations:
procedure get_ref_cursor(the_parameter IN varchar2,
the_sys_refcursor OUT sys_Refcursor) is
begin
open the_sys_refcursor for
select * from (
select 'Greece' as country, 'Athens' as capital from dual
union
select 'Belgium' as country, 'Brussels' as capital from dual
)
where 1=1
and country = the_parameter;
end get_ref_cursor;
begin
DBMS_OUTPUT.put_line('Read the whole recordset:');
get_ref_cursor(
'Greece',
P_Return_Set_O
);
LOOP
FETCH P_Return_Set_O INTO p_column_name, p_column_value;
EXIT WHEN P_Return_Set_O%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(p_column_name || ' ' || p_column_value);
END LOOP;
CLOSE P_Return_Set_O;
--OR:
DBMS_OUTPUT.put_line('Read the recordset and fetch just one column, using the XMLTYPE transformation');
get_ref_cursor(
'Greece',
P_Return_Set_O
);
FOR the_sys_refcursor IN get_columns ('COUNTRY') LOOP
p_column_name := the_sys_refcursor.name;
p_column_value := the_sys_refcursor.value;
DBMS_OUTPUT.put_line(p_column_name || ': ' || p_column_value);
END LOOP;
end;
The code above outputs:
Read the whole recordset:
Greece Athens
Read the recordset and fetch just one column, using the XMLTYPE transformation
COUNTRY: Greece