DBA Hub

📋Steps in this guide1/7

Check the Contents of Ref Cursors

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

oracle miscconfigurationintermediate
by OracleDba
18 views
1

Test Function

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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;
/
2

SQL*Plus and SQLcl Variable

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>
3

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>
4

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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>
5

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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>
6

XML Using XMLTYPE

The data type has a constructor for ref cursors, so we can convert the contents of a ref cursor to XML. Since we are using a function we could call this directly from SQL.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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>

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>
7

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 package. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!