Run Oracle Stored Procedure in Oracle SQL Developer Worksheet

Question

I have a stored procedure with 1 VARCHAR IN and 1 OUT SYS_REFCURSOR Parameter like below :

PROCEDURE do_stg(inParam  IN  VARCHAR2 := NULL, cur OUT SYS_REFCURSOR)
    AS
    BEGIN
    OPEN cur FOR
WITH a AS
( SELECT * FROM aaa WHERE ccc = inParam),
b as 
(SELECT * FROM bbb)
SELECT a.xField, b.yField
  END do_stg;

Before I make some changes to the Stored Procedure, I want to test the changes in an Oracle SQL Developer Worksheet (there are some reasons why I can't change the Procedure directly) to see the results.

I have done this :

DECLARE inParam VARCHAR(50)
BEGIN
WITH a AS
( SELECT * FROM aaa WHERE ccc = inParam),
b as 
(SELECT * FROM bbb)
SELECT a.xField, b.yField
END;

I am expecting to see the result in a grid view as Sproc returns a cursor, but I can't, what I am missing here?

EDIT : My question might be similar, but the answer for my question is not the same with the answer on associated question ; PL/SQL print out ref cursor returned by a stored procedure

as mentioned. For the accepted answer on that question, you have to define all the return types for the cursor TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), ...); --define the record, here not.

there's no stored procedure shown, or any code to execute said stored procedure in your question, so I'm going to guess that you need to use the PRINT command. Examples here https://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/ — thatjeffsmith, Jun 22 '22 at 12:43
Not to mention that this code has several syntax errors, especially missing ';' — gsalem, Jun 22 '22 at 12:47
Grid view shows a result set of a query. Procedure doesn't return a result set, but a cursor in some out parameter. You have to fetch the result of the cursor, for example, by using `print` command on the returned value in a script mode (F5) — astentx, Jun 22 '22 at 13:15

Alex Poole · Accepted Answer · 2022-06-22T13:44:01.497

Another option in SQL Developer is to declare the (modified) procedure locally, and call it with a ref cursor bind variable; something like:

-- client variable
var rc refcursor

DECLARE
-- local version of the procedure
PROCEDURE do_stg(inParam  IN  VARCHAR2 := NULL, cur OUT SYS_REFCURSOR)
    AS
    BEGIN
    OPEN cur FOR
WITH a AS
( SELECT * FROM aaa WHERE ccc = inParam),
b as 
(SELECT * FROM bbb)
SELECT a.xField, b.yField
  END do_stg;
--- end of local procedure

BEGIN
  -- call local procedure with value and ref cursor bind name
  do_stg('some value', :rc);
END;
/

-- print ref cursor
print rc

... and run all of that as a script. The value you pass in can also be a bind variable too, of course.

is there a way to get a Grid version of the result ?

Sort of, if you can create a (temporary) wrapper function:

create or replace function my_wrapper_function (inParam in varchar2 := null)
return sys_refcursor
is
  l_rc sys_refcursor;

-- local version of the procedure
PROCEDURE do_stg(inParam  IN  VARCHAR2 := NULL, cur OUT SYS_REFCURSOR)
    AS
    BEGIN
    OPEN cur FOR
WITH a AS
( SELECT * FROM aaa WHERE ccc = inParam),
b as 
(SELECT * FROM bbb)
SELECT a.xField, b.yField
  END do_stg;
--- end of local procedure

BEGIN
  -- call local procedure with value and ref cursor bind name
  do_stg(inParam, l_rc);
  return l_rc;
END;
/

select my_wrapper_func('some text') from dual;

-- drop function my_wrapper_func;

That will show the results in the grid, but possibly as a single row; you can click the pencil on the right to expand it.

This work marvelous, just what I need, thanks a lot. This outputs to Script Output, is there a way to get a Grid version of the result ? — bbb android, Jun 22 '22 at 13:24
if you want a 'grid' turn your procedure into a function and call it with a SELECT — thatjeffsmith, Jun 22 '22 at 13:35
@thatjeffsmith so Instead of PROCEDURE , should I define it as Function ? — bbb android, Jun 22 '22 at 13:43
@bbbandroid - you can wrap it in a function instead of an anonymous block as I had it; I've updated with an example. — Alex Poole, Jun 22 '22 at 13:44
Ok thanks @AlexPoole. I come from SQL Server background and still couldn't get familiar with not being able to return columns and rows so easily from Oracle :) — bbb android, Jun 22 '22 at 13:51

score 1 · Answer 2 · answered Jun 22 '22 at 12:55

If you want to test the select statement in a SQL Worksheet, your best bet is to use a bind variable in place of your input parameter to your procedure. Something like the statement below will work.

In SQL Developer (as well as other IDEs), when you execute a query that contains a bind variable, you will be prompted to enter a value for the bind variable(s). In the popup, you can enter the values, then after pressing Apply, those values will be used in place of the bind variables in your query.

WITH a AS (
    SELECT
        *
    FROM
        aaa
    WHERE
        ccc = :inparam --Putting ":" before the parameter name will make it a bind variable
), b AS (
    SELECT
        *
    FROM
        bbb
)
SELECT a.xfield, b.yfield from a, b;

EJ actually my problem was more focused on viewing the Cursor returned from the Procedure, but thank you for your advice — bbb android, Jun 22 '22 at 13:27

Run Oracle Stored Procedure in Oracle SQL Developer Worksheet

2 Answers2