11

As a follow-up to the question "Get resultset from oracle stored procedure", is there a way to show the results of a stored procedure that returns a REFCURSOR in a grid (instead of the plain text Script Output window) within SQL Developer?

EDIT: The answer helped, but I'm still having a problem displaying the result set in the "View Value" window:

alt text

The columns can only be expanded a small amount, probably due to the number of results being returned. Expanding the window with the resizer control doesn't help:

alt text

Community
  • 1
  • 1
technomalogical
  • 2,982
  • 2
  • 26
  • 43

1 Answers1

10

I don't think you can with a procedure.

Edit: Thanks to DCookie for simplifying my original answer.

But as a work-around you can write a function that calls the procedure and then invoke that using SQL.

e.g.

create or replace function callmyproc
return sys_refcursor
IS
   rc   sys_refcursor;
BEGIN

   myproc(rc);

   return rc;

END;

Which you can then call with:

   select callmyproc()
   from dual;

When this example is run, the SQL Developer data grid shows one result but if you scroll right and click on the edit button, you will see the results in a grid.

Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
  • 1
    Or simpler yet, create a function that invokes the procedure and returns the refcursor. – DCookie Aug 24 '10 at 22:35
  • @DCookie - Of course! a much more elegant way. Thanks, I've amended my answer. – Ian Carpenter Aug 25 '10 at 07:05
  • Thanks for your help. I've accepted the answer, but for the results I'm getting back the "View Value" window shows tiny columns that I can't expand when clicking on the edit button. – technomalogical Aug 25 '10 at 13:01
  • @technomalogical: I see what you mean, the example is fine but a output with a lot of columns would cause problems. It appears that as the window is resized, the data grid size doesn't change. Sorry I don't know how to get round that problem! – Ian Carpenter Aug 25 '10 at 13:39