8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Check the Contents of Ref Cursors

This article demonstrates a few simple ways to display the contents of ref cursors.

Related articles.

Test Function

We'll use the following function as an example. We could use any cursor variable, a variable of type REF CURSOR, or output parameter from a procedure, but the function will make life easy for the examples.

CREATE OR REPLACE FUNCTION get_ref_cursor
  RETURN SYS_REFCURSOR
AS
  l_cursor   SYS_REFCURSOR;
BEGIN
  OPEN l_cursor FOR
  SELECT level AS id,
         'Description for ' || level AS description
  FROM   dual
  CONNECT BY level <= 5;
  RETURN l_cursor;
END;
/

SQL*Plus and SQLcl Variable

SQL*Plus and SQLcl allow you to define variables of type REFCURSOR. If you assign a cursor variable to them, you can use the PRINT command to print the contents of the variable.

VARIABLE l_cursor REFCURSOR

BEGIN
  :l_cursor := get_ref_cursor;
END;
/

PRINT l_cursor

        ID DESCRIPTION
---------- --------------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5

SQL>

SQL*Plus and SQLcl Implicit Statement Results

Oracle 12c introduced implicit statement results, and SQL*Plus and SQLcl display the contents of implicit statement results.

DECLARE
  l_cursor   SYS_REFCURSOR;
BEGIN
  l_cursor := get_ref_cursor;
  DBMS_SQL.return_result(l_cursor);
END;
/


PL/SQL procedure successfully completed.

ResultSet #1


        ID DESCRIPTION
---------- --------------------------------------------------------
         1 Description for 1
         2 Description for 2
         3 Description for 3
         4 Description for 4
         5 Description for 5

5 rows selected.

SQL>

Manual Approach Using PL/SQL

If you know the internal structure of the ref cursor, you can loop through it and output the contents using PL/SQL.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor       SYS_REFCURSOR;
  l_id           NUMBER;
  l_description  VARCHAR2(100);
BEGIN
  l_cursor := get_ref_cursor;
            
  LOOP 
    FETCH l_cursor
    INTO  l_id, l_description;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_id || ' | ' || l_description);
  END LOOP;
  CLOSE l_cursor;
END;
/
1 | Description for 1
2 | Description for 2
3 | Description for 3
4 | Description for 4
5 | Description for 5

PL/SQL procedure successfully completed.

SQL>

JSON Using APEX_JSON

If you have APEX installed on the database, this is a quick and easy way to display the contents of a ref cursor as a JSON document.

SET SERVEROUTPUT ON
DECLARE
  l_cursor   SYS_REFCURSOR;
BEGIN
  l_cursor := get_ref_cursor;
  
  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('rowset', l_cursor);
  APEX_JSON.close_object;

  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
/
{
"rowset":[
{
"ID":1
,"DESCRIPTION":"Description for 1"
}
,{
"ID":2
,"DESCRIPTION":"Description for 2"
}
,{
"ID":3
,"DESCRIPTION":"Description for 3"
}
,{
"ID":4
,"DESCRIPTION":"Description for 4"
}
,{
"ID":5
,"DESCRIPTION":"Description for 5"
}
]
}

PL/SQL procedure successfully completed.

SQL>

XML Using XMLTYPE

The XMLTYPE data type has a constructor for ref cursors, so we can convert the contents of a ref cursor to XML.

SET SERVEROUTPUT ON
DECLARE
  l_xmltype  XMLTYPE;
BEGIN
  l_xmltype := XMLTYPE(get_ref_cursor);
  DBMS_OUTPUT.put_line(l_xmltype.getClobVal());
END;
/
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <ID>1</ID>
  <DESCRIPTION>Description for 1</DESCRIPTION>
 </ROW>
 <ROW>
  <ID>2</ID>
  <DESCRIPTION>Description for 2</DESCRIPTION>
 </ROW>
 <ROW>
  <ID>3</ID>
  <DESCRIPTION>Description for 3</DESCRIPTION>
 </ROW>
 <ROW>
  <ID>4</ID>
  <DESCRIPTION>Description for 4</DESCRIPTION>
 </ROW>
 <ROW>
  <ID>5</ID>
  <DESCRIPTION>Description for 5</DESCRIPTION>
 </ROW>
</ROWSET>

PL/SQL procedure successfully completed.

SQL>

Since we are using a function we could call this directly from SQL.

SET LONG 1000000
SELECT XMLTYPE(get_ref_cursor) FROM dual;

XMLTYPE(GET_REF_CURSOR)
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
  <ROW>
    <ID>1</ID>
    <DESCRIPTION>Description for 1</DESCRIPTION>
  </ROW>
  <ROW>
    <ID>2</ID>
    <DESCRIPTION>Description for 2</DESCRIPTION>
  </ROW>
  <ROW>
    <ID>3</ID>
    <DESCRIPTION>Description for 3</DESCRIPTION>
  </ROW>
  <ROW>
    <ID>4</ID>
    <DESCRIPTION>Description for 4</DESCRIPTION>
  </ROW>
  <ROW>
    <ID>5</ID>
    <DESCRIPTION>Description for 5</DESCRIPTION>
  </ROW>
</ROWSET>

SQL>

CSV

You can use the csv.sql script to create a package to generate CSV output, including pushing the contents of a ref cursor to the screen using the DBMS_OUTPUT package.

SET SERVEROUTPUT ON
DECLARE
  l_cursor   SYS_REFCURSOR;
BEGIN
  l_cursor := get_ref_cursor;

  csv.output_rc(l_cursor);
END;
/
ID,DESCRIPTION
1,Description for 1
2,Description for 2
3,Description for 3
4,Description for 4
5,Description for 5

PL/SQL procedure successfully completed.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.