How to execute utplsql for collection data type

Question

I want support to handle collection datatype in ut_expect in utplsql.

I want to do the testing of below package using utplsql. I want to check the date array.

create or replace PACKAGE pkg_BA_GetAccrualHist
AS
   TYPE arr_start_date IS TABLE OF VARCHAR2(12)
      INDEX BY BINARY_INTEGER ;

      CURSOR cur_BA_GetAccrualHist
      (
         p_ref_RefNum             IN  VARCHAR2(15)

      )
   IS
      SELECT TO_CHAR(A.start_date, 'MON DD,YYYY'),
              FROM ACCRUAL_HIST A
       WHERE A.ref_num           = p_ref_RefNum
               ORDER BY A.start_date;

    PROCEDURE sp_BA_GetAccrualHist_s
        (
            p_ref_RefNum         IN      accrual_hist.ref_num%TYPE,
            p_dat_StartDate         OUT  arr_start_date,
        );

END pkg_BA_GetAccrualHist;
create or replace PACKAGE BODY pkg_BA_GetAccrualHist
AS

   PROCEDURE sp_BA_GetAccrualHist_s
   (
        p_ref_RefNum         IN      accrual_hist.ref_num%TYPE,
        p_dat_StartDate         OUT  arr_start_date
    ) IS 

          i INTEGER ;

   BEGIN

      IF NOT cur_BA_GetAccrualHist%ISOPEN
      THEN
         OPEN cur_BA_GetAccrualHist (p_ref_RefNum);

     END IF;

      FOR i IN 1.. 10
      LOOP

         FETCH cur_BA_GetAccrualHist
         INTO  p_dat_StartDate(i);

         IF cur_BA_GetAccrualHist%NOTFOUND
         THEN
            CLOSE cur_BA_GetAccrualHist;
        end IF;
    END LOOP;
   END sp_BA_GetAccrualHist_s;
END pkg_BA_GetAccrualHist;

MY approach is 
create or replace package UT_pkg_BA_GetAccrualHist as
  --%suite(SUITE FOR pkg_BA_GetAccrualHist)

  --%test(TC1 When all the parameters are passed as '')
  procedure UT_sp_BA_GetAccrualHist_T1;

end;

create or replace PACKAGE BODY UT_pkg_BA_GetAccrualHist AS

    PROCEDURE UT_sp_BA_GetAccrualHist_T1 IS
    P_REF_REFNUM VARCHAR2(15);
    P_DAT_STARTDATE TRIMS.PKG_BA_GETACCRUALHIST.ARR_START_DATE;
    BEGIN
      P_REF_REFNUM := '123456789';

      PKG_BA_GETACCRUALHIST.SP_BA_GETACCRUALHIST_S(
    P_REF_REFNUM => P_REF_REFNUM,
    P_DAT_STARTDATE => P_DAT_STARTDATE);

After that I don't know how to handle the collection data type in ut_expect. I have tried many thing but won't work.

score 0 · Answer 1 · answered Aug 01 '19 at 18:39

Unfortunately your question doesn't specify what you have tried and how it didn't work, but in general it is possible to write expectations for collections, but it does require a bit of extra work.

The trick is in using anydata.convertCollection(), as this snipped from the documentation shows (in this case for a table of objects, but should be similar for just a table of varchars):

create type department as object(name varchar2(30))
/
create type departments as table of department
/

procedure test_departments is
  v_expected departments;
  v_actual   departments;
begin
  v_expected := departments(department('HR'), department('IT') );
  ut.expect( anydata.convertCollection( get_depts() ) ).to_equal( anydata.convertCollection( v_expected ) );
end;

See Object and nested table data-type comparison examples for more elaborate examples on objects, nested tables and other complex types.

How to execute utplsql for collection data type

1 Answers1