Easiest method to test an Oracle Stored Procedure

I’m working on an ASP.NET project with an Oracle Database. We are using TOAD to add/manage the stored procedures — and in general I like TOAD and Oracle. The one thing I’ve found frustrating is finding a simple way to test an Oracle Stored Proc, such as SQL Server’s “exec [SP_NAME] Param1, Param2, ParamN” syntax.

All of our stored procedures output Ref Cursors. Here is an example of a Stored Proc:

CREATE OR REPLACE PROCEDURE APP_DB1.GET_JOB
(
    p_JOB_ID IN JOB.JOB_ID%type,
    outCursor OUT MYGEN.sqlcur
)
IS
BEGIN
    OPEN outCursor FOR
    SELECT *
    FROM JOB
    WHERE JOB_ID = p_JOB_ID;
END GET_JOB;
/

Any suggestions?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You just need a script that calls your stored procedure and has a bind variable for the ref cursor output to display it in TOAD’s grid in the Editor window.

DECLARE
 type result_set is ref cursor; 
BEGIN
 APP_DB1.GET_JOB(1, :result_set);
END;

When you then run this TOAD will prompt you to ‘bind’ :result_set, just select ref cursor from the list of types and then the result will display in the grid. The trick is to think of yourself as a ‘client’ calling your stored procedure and you need your own ref cursor to store the result.

Method 2

If you just looking for a way to invoke the SP, then the Oracle way is:

begin
  sp_name(....);
end;

I don’t use Toad, but you should be able to put this into a SQL window and execute it.

Method 3

In sqplus you can use the syntax

SQL>var rc refcursor

SQL>exec APP_DB1.GET_JOB(the job id you want to query, :rc)

SQL>print rc

That should do it. The first line defines a bind variable. You could also define a variable for the job id, or just type it in.

Method 4

TOAD shows the result in a grid just fine with sample script from Russel. Run as script.

variable  P_CUR refcursor;
exec  PACK.GETEXECUTION ( '9f363e49-88c1-4295-b61e-60812d620d7e', '6', :P_CUR );
print P_CUR;

Thanks!

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0