What’s the easiest way to test an Oracle stored procedure?

1160680 pts.
Tags:
Oracle
Stored Procedures
We've been working on an ASP.NET project that's using an Oracle database. We've been using TOAD to add and manage stored procedures. This is where the problem lies....we're having too much trouble testing an Oracle stored procedure. Here's an example of one of our stored procedures:
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;
/
Can someone tell me of an easier way to test these procedures? Appreciate the help.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,

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. If you just looking for a way to invoke the SP, then the Oracle way is:

begin
  sp_name(....);
end;

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: