5 Replies Latest reply on Nov 25, 2014 9:01 PM by thatJeffSmith-Oracle

    Unit Testing: Different column length calculation in Expected and Received record sets leads to false failure of a test

    val.2046

      Dear SQL Developer team,

       

      It looks like the column length calculation in the Expected and Received record sets behave differently in some cases (when stored proc variables are being used in the REF CURSOR generation, despite the record type explicit definition): the header length and value length in the Received record set are being truncated to some arbitrary length based on the value returned, 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 lengths. Please see the screenshot below:

       

      Expected_and_Received_RecordSets.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. Alter the HR.EMPLOYEES table with adding a new VARCHAR2(4000) column LONG_LAST_NAME:

       

      ALTER TABLE HR.EMPLOYEES

      ADD (LONG_LAST_NAME VARCHAR2(4000));

       

      update hr.employees set long_last_name = last_name;

      commit;

       

       

      3. Create a package PKG_TEST2 with the source code below in the HR schema:

       

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

       

      create or replace PACKAGE PKG_TEST2 AS

       

      TYPE EmployeeInfoRec IS RECORD

          (

          long_last_name  employees.long_last_name%TYPE,

          first_name employees.first_name%TYPE,

          email      employees.email%TYPE

          );

      TYPE EmployeeInfoRecCur IS REF CURSOR RETURN EmployeeInfoRec;

       

      FUNCTION getEmployeeInfo (p_Emp_Id employees.employee_id%TYPE)

      RETURN EmployeeInfoRecCur;

       

      END PKG_TEST2;

       

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

       

      CREATE OR REPLACE PACKAGE BODY PKG_TEST2 AS

       

      FUNCTION getEmployeeInfo (p_Emp_Id employees.employee_id%TYPE)

        RETURN EmployeeInfoRecCur AS

        v_EmployeeInfoRecCur EmployeeInfoRecCur;

        v_LongLastName varchar2(4000);

      BEGIN

        select long_last_name into v_LongLastName

        from employees

        where employee_id = p_Emp_Id;

        --

        OPEN v_EmployeeInfoRecCur FOR

          SELECT v_LongLastName long_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_TEST2;


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

       

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

       

      5. Update the default Dynamic Value Query with the one below and save/commit changes.

       

      select cursor(         

      SELECT long_last_name,         

      first_name,         

      email         

      FROM employees    

      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     

       

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

       

       

      So, the "EmployeeInfoRec" record type in the package clearly defines the LONG_LAST_NAME as VARCHAR2(4000) via reference to the actual column data type in the referenced table.

       

      But for some reason the SQL Developer does not properly calculate its length in the "Received" recordset if a variable is being used (could be a simple varchar2 variable like in this reproducible test case or a complex variable of object type).

       

      Any thoughts on this? Looks like another bug...

       

      Thanks,

       

      Val