Call stored procedure in SQL Developer that returns resultset?

Question

I'm new to Oracle, so please bear with me. I've been searching, but I haven't found a straight answer to this.

In SQL Developer, I created the following stored procedure. I assume it was created because it returns message Procedure ORACLE_GET_TOP_N_TESTS compiled:

create procedure oracle_get_top_n_tests
(oracle_tests OUT SYS_REFCURSOR) 
AS 
BEGIN 
OPEN oracle_tests FOR
select * from sales.quiz_results;
END oracle_get_top_n_tests;
/

Now I would like to call/execute the stored procedure to view the resultset (the select statement) . I've tried the following, but I see no resultset:

variable mycursor refcursor;
exec oracle_get_top_n_tests ( :mycursor );

/* Received message PL/SQL procedure successfully completed, but no result */

What am I doing wrong?

I use Database 11g Express Edition.

Answer 1

You are getting the result, you just aren't doing anything with it. You can print it:

variable mycursor refcursor;
exec oracle_get_top_n_tests ( :mycursor );
print mycursor

The output will go in the script output window, not in a grid. (You would need to make it a function, or use a wrapper function, to get the output in a grid, and even then there's an extra step to view them.)

---

Simple wrapper function:

create function oracle_get_top_n_tests_wrapper
RETURN SYS_REFCURSOR
AS
  oracle_tests SYS_REFCURSOR;
BEGIN
  oracle_get_top_n_tests (oracle_tests => oracle_tests);

  return oracle_tests;
END oracle_get_top_n_tests_wrapper;
/

then call that as:

select oracle_get_top_n_tests_wrapper from dual;

If you run that as a script then the results will still go in the script output window; if you run it as a statement then they will go in the results grid but as a single row/column, which you can expand to see the actual contents.

---

If you aren't stuck with a procedure then you could replace that with a function instead, which then wouldn't need a wrapper:

-- drop function oracle_get_top_n_tests_wrapper
-- drop procedure oracle_get_top_n_tests

create function oracle_get_top_n_tests
RETURN SYS_REFCURSOR
AS
  oracle_tests SYS_REFCURSOR;
BEGIN
  OPEN oracle_tests FOR
  select * from sales.quiz_results;

  return oracle_tests;
END oracle_get_top_n_tests;
/

and then call that directly:

select oracle_get_top_n_tests from dual;

with the same notes on the results that applied to the wrapper.