How to see refcursor result/output in Oracle SQL D

问题:

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.

.

回答1:

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

回答2:

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;