8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Check the Contents of Ref Cursors
This article demonstrates a few simple ways to display the contents of ref cursors.
- Test Function
- SQL*Plus and SQLcl Variable
- SQL*Plus and SQLcl Implicit Statement Results
- Manual Approach Using PL/SQL
- JSON Using APEX_JSON
- XML Using XMLTYPE
- CSV
Related articles.
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Implicit Statement Results in Oracle Database 12c Release 1 (12.1) (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT)
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- XMLTYPE Datatype
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:
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Implicit Statement Results in Oracle Database 12c Release 1 (12.1) (DBMS_SQL.RETURN_RESULT and DBMS_SQL.GET_NEXT_RESULT)
- APEX_JSON Package: Generate and Parse JSON Documents in Oracle
- XMLTYPE Datatype
Hope this helps. Regards Tim...