Oracle: How to Test Stored Procedure

Ref. Cursor is used to get back data from oracle in data set or cursor format. Sometimes it happens that the front end is not ready and you may need to test the procedure for the result set. It’s very easy and simple to test a stored procedure which returns data in forms of Ref. Cursor.

Let’s say you have stored procedure sp_myFirst_proc and there is 2 input parameters and 1 output parameter to return the data (ref. cursor data type). To verify the data, first logged in into Oracle Sql*Plus, once logged in you will have command window showing the sql> prompt. Type as below:

sql>variable myData refcursor;
sql>execute sp_myFirst_proc('01101', '07/06/2004', :myData);
sql>print myData;

01101 and 07/06/2004 these two are input parameters and myData gets the data returned by procedure.

This entry was posted in Oracle, SQL, Stored Procedure and tagged , , , , , . Bookmark the permalink.

One Response to Oracle: How to Test Stored Procedure

  1. Andres says:

    Prueba con esto

    CREATE OR REPLACE PROCEDURE SPRUEBA (vardes in varchar2(30))
    IS
    TYPE CUR_TYP IS REF CURSOR;
    c_cursor CUR_TYP;
    fila mi_tabla %ROWTYPE;
    v_query VARCHAR2(255);
    BEGIN

    v_query := ‘SELECT * FROM mi_tabla where descripcion = ‘|| ””||vardes||””;

    OPEN c_cursor FOR v_query;
    LOOP
    FETCH c_cursor INTO fila;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(fila.Campo1);
    END LOOP;
    CLOSE c_cursor;
    END;

    Saludos Ing. Andres