11

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.

.

Community
  • 1
  • 1
Prabin Yovan
  • 164
  • 1
  • 3
  • 14
  • what output are you expecting? where do the 'results' of your sp go? you probably want to write a SELECT statement against that... – Randy Dec 22 '11 at 22:46
  • Expecting a result set of row and columns. SP results go into a .NET progam. How do i write a select against the cursor... some sample would be nice.. – Prabin Yovan Dec 22 '11 at 22:52
  • Use Toad instead, it will display the contents of a refcursor for you. – Igby Largeman Dec 23 '11 at 16:10

2 Answers2

24

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...

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 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](http://stackoverflow.com/questions/3526798/best-way-tool-to-get-the-results-from-an-oracle-package-procedure/3527037#3527037) of my own though. – Alex Poole Dec 23 '11 at 16:07
  • 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`. – WSC Mar 11 '20 at 14:28
  • @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 Mar 11 '20 at 16:28
  • @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](https://pasteboard.co/IZlCiuP.png). The error really suggests you just used `r` instead of `:r` in your call. – Alex Poole Mar 16 '20 at 11:02
  • 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... – WSC Mar 16 '20 at 13:20
  • 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 – Mano Feb 10 '21 at 12:53
-3

To view your cursor results you need to loop through your cursor and print values. You need to know column names for what your cursor is returning. You can do something like:

DECLARE
   type output_cursor is ref cursor;
   P_CURSOR output_cursor;
BEGIN
   P_CURSOR := NULL;
   DOCTORS_APP.get_reminders (  P_NOTIFICATION_ID => 1975357,P_CURSOR => P_CURSOR) ;
   //replace Column1 and Column2 with actual column names
   FOR CUR_VAL in P_CURSOR LOOP
        DBMS_OUTPUT.PUT_LINE(CUR_VAL.Column1||' '||CUR_VAL.Column2);
   END LOOP;
 END;
Emmanuel N
  • 7,350
  • 2
  • 26
  • 36
  • 1
    I tried the sample, its gives error. – Prabin Yovan Dec 22 '11 at 23:22
  • what error? Have you repleced Column1 and Column2 with actual column names? – Emmanuel N Dec 22 '11 at 23:24
  • 1
    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: – Prabin Yovan Dec 22 '11 at 23:25
  • The code above does not actually work. – OverMars Nov 04 '15 at 16:36