Updated Feb 5, 2021
There are two ways you can view your REFCURSOR output from calling PL/SQL stored procedures and functions in Oracle SQL Developer.
Using the PRINT command in a SQL Worksheet
Using the classic SQL*PLUS PRINT command to view the refcursor output will work in SQL Developer just like it would work in your command line tools. You execute your program, you create a local variable or 3 to ‘catch’ said output, and then you PRINT it.
It’s good, but if you’re using a tool, you’re not really taking advantage of the tool. I do think it’s good to know how it works, because sometimes all you have is the command line.
Let’s look at a VERY simple example:
-- I don't want to see ANY code like this in production, -- but it's about as simple as we can make it for an example CREATE OR REPLACE FUNCTION refcursor_function RETURN SYS_REFCURSOR AS c SYS_REFCURSOR; BEGIN OPEN c FOR SELECT * FROM hr.countries; RETURN c; END; / -- And then we need to call said FUNCTION, and we'll do it with a SELECT INTO.
Using the Procedure Editor / Execute Button
When you open the plsql program from your connection tree, OR open it from a file and associate it with a database connection… and execute your function, SQL Developer automatically adds some code to the ANON block that allows us to grab the refcursor coming back from the database and assign it to a grid in SQL Developer.
You can see this code here
DECLARE v_Return SYS_REFCURSOR; BEGIN v_Return := REFCURSOR_FUNCTION(); /* Legacy output: DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return); */ :v_Return := v_Return; --<-- Cursor END;
You might initially think that this isn’t working in SQL Developer. Be patient! You need to click on the ‘Output Variables’ panel.
Your output window will now have an ‘Output Variables’ pane that has a grid with the first 100 records in your cursor.