4 Replies Latest reply on Oct 16, 2014 7:29 PM by val.2046

    Unit Testing: Different header behavior in Expected and Received record sets leads to false failure of a test

    val.2046

      Dear SQL Developer team,

       

      It looks like the headers in the Expected and Received record sets behave differently: the expression headers in the Expected record set are being truncated to 30 characters and the same expression headers in the Received record set are not being truncated, which leads to a false failure of a unit test. I.e. the records are the same in both record sets but the test run fails the record sets comparison because of the different headers. Please see the screenshot below:

       

      Expected_and_Received_Headers.jpg

       

      Here is the test case if you would need to reproduce the issue/bug in your environment:

       

      1. Use the default HR schema from the Oracle Examples package that comes with a 11g database.

      2. Create a package PKG_TEST with the source code below in the HR schema:

       

      ----------------------------------------------------------------------------------------------------------------------

      create or replace PACKAGE PKG_TEST AS

       

      TYPE EmployeeInfoRec IS RECORD

          (

          last_name  employees.last_name%TYPE,

          first_name employees.first_name%TYPE,

          email      employees.email%TYPE

          );

      TYPE EmployeeInfoRecCur IS REF CURSOR RETURN EmployeeInfoRec;

       

      FUNCTION upperCaseLastName (p_Last_Name employees.last_name%TYPE)

      RETURN varchar2;

       

      FUNCTION getEmployeeInfo (p_Emp_Id employees.employee_id%TYPE)

      RETURN EmployeeInfoRecCur;

       

      END PKG_TEST;

      ----------------------------------------------------------------------------------------------------------------------

      CREATE OR REPLACE PACKAGE BODY PKG_TEST AS

       

      FUNCTION upperCaseLastName (p_Last_Name employees.last_name%TYPE)

      RETURN varchar2 AS

      BEGIN

        RETURN upper(p_Last_Name);

      END upperCaseLastName;

       

      FUNCTION getEmployeeInfo (p_Emp_Id employees.employee_id%TYPE)

        RETURN EmployeeInfoRecCur AS

        v_EmployeeInfoRecCur EmployeeInfoRecCur;

      BEGIN

        OPEN v_EmployeeInfoRecCur FOR

          SELECT upperCaseLastName(last_name),

                 first_name,

                 email

           FROM employees this_is_very_long_table_alias

           WHERE employee_id = p_Emp_Id

           order by 1 asc;

        --

        RETURN v_EmployeeInfoRecCur;

      EXCEPTION

        WHEN OTHERS THEN

          RAISE;

      END getEmployeeInfo;

       

      END PKG_TEST;

      ----------------------------------------------------------------------------------------------------------------------

       

      3. Create a unit test for the PKG_TEST.getEmployeeInfo store procedure: (click Create Test command, select the stored proc, click Ok to popup message, click Finish).

       

      4. Update the default Dynamic Value Query with the one below and save changes.

       

      select cursor(   

      SELECT pkg_test.upperCaseLastName(last_name),   

      first_name,   

      email   

      FROM employees this_is_very_long_table_alias  

      WHERE employee_id = idqry.employee_id   

      order by 1 asc   

      ) as RETURNS$,     

      idqry.employee_id as P_EMP_ID     

      from (select employee_id    

      from employees    

      where rownum<=5) idqry

       

      5. Run the newly created unit test in the debug mode to see the screen shot above.

       

      The work around is to use a short alias for the long expression, but our code is in Production and we do not plan a new release any time soon to add aliases... but we do plan to use SQL Developer to automate our PL/SQL code unit testing once we clear some of the issues with the tool that we are experiencing...

       

      Please advise...

       

      Thanks,

       

      Val