4 Replies Latest reply: Apr 3, 2013 11:50 PM by 982638 RSS

    Unit Test Validation for Output Ref Cursor Not Working

    982638
      Here is the problem:

      I have a stored procedure as follows:

      CREATE OR REPLACE
      PROCEDURE usp_GetEmployee(
      p_employeeId IN NUMBER,
      cv_employee OUT Sys_RefCursor )
      AS
      BEGIN
      OPEN cv_employee FOR SELECT * FROM employees WHERE employee_id=p_employeeid;
      END usp_GetEmployee;

      For this, I am implementing a unit test.
      * In the "Select Parameters" step, I am unchecking the "Test Result" check box for the cursor OUT variable.
      * In the "Specify Validations" step, I am choosing "Boolean Function" and putting the following PL/SQL code:

      DECLARE
      emp_rec {cv_employee$}%rowtype;
      BEGIN
      FETCH {cv_employee$} INTO emp_rec;
      IF {cv_employee$}%FOUND THEN
      RETURN TRUE;
      ELSE
      RETURN FALSE;
      END IF;
      RETURN TRUE;
      END;

      But, when I try to execute this Test, I get the following error:

      Validation Boolean function failed: Unable to convert <oracle.jdbc.driver.OracleResultSetImpl@4f0617> to REF CURSOR.

      If I run in the debug mode, I get the following content in a dialog box:

      The following procedure was run.

      Execution Call
      BEGIN
      "ARCADMIN"."USP_GETEMPLOYEE"(P_EMPLOYEEID=>:1,
      CV_EMPLOYEE=>:2);
      END;

      Bind variables used
      :1 NUMBER IN 1001
      :2 REF CURSOR OUT (null)

      Execution Results
      ERROR
      CV_EMPLOYEE : Expected: [Any value because apply check was cleared], Received: [EMPLOYEE_ID                             COMMISSION_PCT                          SALARY                                 
      --------------------------------------- --------------------------------------- ---------------------------------------
      1001                                    0.2                                     8400                                   
      ]
      Validation Boolean function failed: Unable to convert <oracle.jdbc.driver.OracleResultSetImpl@31dba0> to REF CURSOR.




      Please suggest how to handle this issue.


      Thanks,
      Rahul