Oracle PL/SQL: Read just one column from SYS_REFCURSOR using XML transformation

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

Leave a Reply