Toad World® Forums

Error while running test cases for Package


#1

Hi All,

Please check below package

CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;

==============================================
CREATE OR REPLACE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
CURSOR c1 IS (SELECT ENAME FROM EMP WHERE EMPNO=7839);
RC_EMP C1%ROWTYPE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
OPEN C1;
FETCH C1 INTO RC_EMP;
DBMS_OUTPUT.PUT_LINE(RC_EMP.ENAME);
END;
PROCEDURE print_pkg_state IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Num: ’ || pkg1.num);
END;
END pkg1;

Mentioned SERIALLY_REUSABLE in package specification as well as in body.
In package body declared private cursor which is being used in package procedures and functions.

Open and fetch the value of cursor in init_pkg_state procedure, but did not close the cursor.
Due pragma cursor will be closed automatically when procedure completed.

When i am trying to create test case for the procedure is giving me error, cursor is already open.

If i am running the procedure in same session on sql prompt it is working fine.

Please suggest…

Thanks
Sandeep


#2

Actually, the cursor is closed when the current database call is
terminated, not when the procedure finishes running. Since all the
running of the tests occur within a single call from the Delphi front
end to the Oracle backend, the cursor is not automatically closed. I
think that in this situation, you will have to explicitly close the
cursor after the test case is completed.


#3

Thanks Steven for reply.

It is amazing.I am not able think how it is implemented. For each inputs the procedure should call everytime or it is handled through collection?

Yeah… If we want to test it through QCTO, then only need to close it or it may happen in any other senario.

Thanks
Sandeep


#4

For each test case, Code Tester calls your subprogram with the specified inputs, all from within a single call to the test package for the subprogram.

SF