CODEWITHSUNDEEP
asp.netoracle-database
Josh
Josh

Reputation: 8477

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?

Upvotes: 10

Views: 39141

Answers (5)

Samuel Nyong'a
Samuel Nyong'a

Reputation: 7

The idea is just to bind the outCursor variable to a cursor when toad prompts you for the variable type. Just pass the other variables the usual way. Like in the example below.

BEGIN
 APP_DB1.GET_JOB(1, :v_outCursor);
END;

Run it and a dialogue box will prompt you to bind the :outCursor variable as shown in the following image.

outCursor variable

Toad will then display the result in the result grid.

Upvotes: 0

Daniel Carvalho Liedke
Daniel Carvalho Liedke

Reputation: 1

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!

Upvotes: 0

Brian
Brian

Reputation: 13571

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.

Upvotes: 13

RussellH
RussellH

Reputation: 1245

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.

Upvotes: 2

dpbradley
dpbradley

Reputation: 11915

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.

Upvotes: 1

Related Questions