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.

Alex Poole · Accepted Answer · 2019-06-27T16:46:18.370

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.

Thanks. Do you think you can include a simple example of a function with a `select` statement? — fdkgfosfskjdlsjdlkfsf, Jun 27 '19 at 15:33
Thanks, but I wasn't referring to a function that would act as a wrapper of another object. I was referring to a function that includes a `select` statement. — fdkgfosfskjdlsjdlkfsf, Jun 27 '19 at 16:27
In your answer you write _make it a function, or use a wrapper function_. I assume the example you posted is a wrapper function that wraps the stored procedure. — fdkgfosfskjdlsjdlkfsf, Jun 27 '19 at 16:42
Instead of a wrapper function for a stored procedure with a `select` statement, it would be a function with a `select` statement. — fdkgfosfskjdlsjdlkfsf, Jun 27 '19 at 16:45
I see; OK, I've added that too. The next step on from that would be a function returning a (pipelined) table, as @ValérioCosta alluded to. — Alex Poole, Jun 27 '19 at 16:46

score 0 · Answer 2 · answered Jun 27 '19 at 14:57

0

You can just run the select statement in the worksheet to see the result.

If you want an object returning the resultset i would go with a function that returns a table

simple tutorial where i learned that: Tutorial

then you just run the statement in the worksheet select * from table(function_oracle_get_top_n_tests);

answered Jun 27 '19 at 14:57

Valério Costa

425
1
3
17

Thanks. Do you think you can include an example with a simple `SELECT` statement? – fdkgfosfskjdlsjdlkfsf Jun 27 '19 at 15:27

Call stored procedure in SQL Developer that returns resultset?

2 Answers2