If you work with PL/SQL, then this feature might be the #1 reason I recommend you immediately upgrade to at least version 3.0 of Oracle SQL Developer –
SQL Developer now automatically renders SYS.REFCURSOR output from your PL/SQL calls.
Prior to version 3.0, you needed to use the SQL*PLUS PRINT command to view the refcursor output. I hated this. Now in v3.0 and higher, it’s just automatic. No options to enable, no widgets to mess with, just hit the ‘execute’ button and you are good to go. This is a good example of the Developer Exchange having an impact on the product. Many of you voted for this feature, and now it is here. It would have happened eventually, but customer demand does have a big impact when it comes to prioritizing development requests.
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;
Execute the PL/SQL
When you open 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.