ThatJeffSmith

SQL Developer Tip: Viewing REFCURSOR Output

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.

You're only 1 click away!

Your output window will now have an ‘Output Variables’ pane that has a grid with the first 100 records in your cursor.

SQL Developer makes debugging SYS.REFCURSORS easy!