ThatJeffSmith

SQL Developer Tip: Viewing REFCURSOR Output

Social:

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:

[sql]
— 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;
[/sql]

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
[sql]
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;
[/sql]

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!

Related Posts Plugin for WordPress, Blogger...
Social:

Similar Posts by Content Area: , , ,