How to see refcursor result/output in Oracle SQL Developer?

Question

Possible Duplicate:
Best way/tool to get the results from an oracle package procedure
Oracle SQL Developer: Show REFCURSOR Results in Grid?

I am new to Oracle SQL Developer. I am using Oracle SQL Developer Version 3.0. I was trying to test my SP using the following query.

DECLARE
  type output_cursor is ref cursor;
  P_CURSOR output_cursor;
BEGIN
  P_CURSOR := NULL;
  myPackage.mySPTest (  P_NOTIFICATION_ID => 1975357,P_CURSOR => P_CURSOR) ;
END;

When I ran the above query in my Oracle SQL Developer, I am getting a message 'anonymus block completed' and its not showing any result.

Can anyone help me, how to see the result.

.

Solution

You can use a bind variable declared in SQL Developer to hold and show the results:

var r refcursor;
exec myPackage.mySPTest(P_NOTIFICATION_ID => 1975357, P_CURSOR => :r);
print r;

exec is shorthand for an anonymous block so this is equivalent to:

var r refcursor;
begin
    myPackage.mySPTest(P_NOTIFICATION_ID => 1975357, P_CURSOR => :r);
end;
/
print r;

Unless P_CURSOR is declared as something unhelpful, maybe...

Comments

[Prabin Yovan]

I tried the sample, its gives error.

[Emmanuel N]

what error? Have you repleced Column1 and Column2 with actual column names?

[Prabin Yovan]

error Error starting at line 1 in command: Error report: ORA-06550: line 11, column 10: PLS-00221: 'P_CURSOR' is not a procedure or is undefined ORA-06550: line 11, column 1: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

[Alex Poole]

Ah, I hadn't noticed @sathya had put two links in his comment on the question; the second (and a link in the first) point to Tony Andrews doing the same thing. I was going from an old answer of my own though.

[OverMars]

The code above does not actually work.

[WSC]

I know this is a really old answer but it's still a top result in Google... This doesn't workin SQL Developer 18 with Oracle 11g. It just throws Ora-06550 - identifier 'R' must be declared.

[Alex Poole]

@WSC - I can't check right now but I'm at least 99% sure I've done exactly this with those versions too. You used the colon in the right place?

[Alex Poole]

@WSC - nope, definitely works exactly as posted in 18.1, 18.2, 18.3 and 18.4 against an 11.2.0.4 DB. This is from 18.4. The error really suggests you just used r instead of :r in your call.

[WSC]

Ah, I think you might be right. I've just tried again in SQL Developer and SQL*Plus and it does work as you said. Not entirely sure how I managed that as I could swear I copied and pasted it to make sure the format was exactly the same...

[Mano]

The first block with 'exec' doesn't work for Oracle 19c on SQL Developer 3.2.20.10. However, the second option with begin......end works fine. Thank you