Easiest method to test an Oracle Stored Procedure

Question

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?

Answer 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.

Answer 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.

Answer 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.