How to Print ref cursor Output in SQL Developer

In this tutorial, we will explain how to display ref cursor output in Oracle using SQL Developer step by step. We will use a function that returns a ref cursor as an example and procedure without a parameter of the type ref cursor.

Steps to Print ref cursor Output in SQL Developer

Create a test function to print its result
Execute the function
View the output
Verify the result set

Using SQL Developer, we can fetch sys_refcursor and print output in a very simple 4 steps that we will go through one by one in this article.

Whether you have a function that returns a ref cursor or a procedure that has out the variable of type ref cursor, you can print the result in both cases. And we will see both examples here.

Example using PL/SQL Function

Create Test Function to Print it’s Ref Cursor Result

We will create a function that returns the ref cursor to use as an example.

-- EMP_REFCURSOR_F function to print a ref cursor output
-- Created by : Hassan for Oraask.com as example

CREATE OR REPLACE FUNCTION EMP_REFCURSOR_F
  RETURN SYS_REFCURSOR
AS
  L_EMP_REF_CURSOR   SYS_REFCURSOR;
BEGIN
  OPEN L_EMP_REF_CURSOR FOR
    SELECT     LEVEL AS FUNCTION_COLUMN1, DBMS_RANDOM.STRING ('A', TRUNC (DBMS_RANDOM.VALUE (1, 50))) FUNCTION_COLUMN2
    FROM       DUAL
    CONNECT BY LEVEL <= 5000;

  RETURN L_EMP_REF_CURSOR;
END;

Execute The Function

Click Run or press (CTRL+F10)

Print ref cursor Output in SQL Developer Figure 1 — Figure 1

Or from the connections panel on the right side expand your schema objects then function (You can apply a filter before expanding the functions node to filter by your function name which is “EMP_REFCURSOR_F” in our case to reduce the time of getting all the functions available in your schema) then Right Click and then click Run.

Print ref cursor Output in SQL Developer Figure 2 — Figure 2

Then you will see the following screen

Print ref cursor Output in SQL Developer Figure 3 — Figure 3

Here SQL Developer created a local variable to store the result set of the ref cursor returned from the function.

View the Output

Go to Output Variables – log -> click on SQL Developer output variables tab

Print ref cursor Output in SQL Developer Figure 4 — Figure 4

Verify the Result Set

As we built our ref cursor query that has two columns one is representing the ID and the other column has a random character mix of capital and small case.

Create Test Procedure to Print it’s Ref Cursor Result

We will create a function that returns the ref cursor to use as an example.

-- EMP_REFCURSOR_F procedure to print a ref cursor output
-- Created by : Hassan for Oraask.com as example

CREATE OR REPLACE PROCEDURE EMP_REFCURSOR_P (O_REFCURSOR OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN O_REFCURSOR FOR
    SELECT     LEVEL AS COLUMN1, DBMS_RANDOM.STRING ('X', TRUNC (DBMS_RANDOM.VALUE (1, 50))) COLUMN2
    FROM       DUAL
    CONNECT BY LEVEL <= 5000;
END EMP_REFCURSOR_P;

Do you know: How to Generate DDL Scripts for All Tables in SQL Developer

Execute The Procedure

Click Run or press (CTRL+F10)

Print ref cursor Output in SQL Developer Figure 5 — Figure 5

Or from the connections panel on the right side expand your schema objects then procedure and then Right Click and then click Run.

Print ref cursor Output in SQL Developer Figure 6 — Figure 6

Then you will see the following screen

Print ref cursor Output in SQL Developer Figure 7

As you can see SQL Developer created a local variable to store the result set that returned from the ref cursor out parameter in our procedure.

View the Output

Go to output Variable – log -> click on output variables

Print ref cursor Output in SQL Developer Figure 8 — Figure 8

Verify the Result Set

As we built our ref cursor query that has two columns one is representing the ID and the other column has a random alphanumeric character in a capital case.

Conclusion

There are many ways for how to print ref cursor output in Oracle. Here in this article, we covered how to display ref cursor output in SQL Developer tool by using two examples one with function and the other with procedure.

If you have any questions, please don’t hesitate to ask them in the comments section below. or ask your question from up right corner and an expert will reply your inquery.
If you have an addition to this guide to add extra value for the readers, kindly let me know.
Hopefully, it was clear and concise.