Skip to content

Ignore unit test annotation

Newly introduced annotation @IgnoreUnitTest can be used with PLSQL Functions/Procedures if you find those methods not Unit-testable or not applicable for Unit testing.

Following concerns will be addressed by using IgnoreUnitTest annotation

  1. When a developer finds that a particular method is not applicable for unit testing, another developer needs not go through the same investigation and spend time if the method is already annotated.

  2. Annotated methods will get excluded when generating statistics from the PLSQL Unit Test Coverage tool and it helps to show the correct picture of the remaining methods which need to be covered by the unit tests.

At the part developer can mention the reason to ignore unit testing for a particular method. At the moment we have identified several reasons to ignore unit testing.

Developers can get annotations throughh Developer studio autosuggestions. (CTRL + SPACE)

Ignore unit test annotation auto suggestion

Applicable annotation comments

  • DMLOperation

  • BLOBDataType

  • MethodOverriding

  • PipelinedFunction

  • NoOutParams

  • PLSQLInSQL

  • DynamicStatement

  • TrivialFunction

Annotation comment information

✔DMLOperation

@IgnoreUnitTest DMLOperation

Unit tests are supposed to be lightweightand test only the logic written in a particular method and will use mocking to avoid executing any other logic written outside the method being unit tested. Therefore, the PLSQL unit test framework will not support testing DML. Use integration tests (TAR) for testing broader scopes

Sample method (ORDER/CustomerAgreement.plsql)

@IgnoreUnitTest DMLOperation
PROCEDURE Set_Msg_Sequence_And_Version (
   agreement_id_ IN VARCHAR2,
   sequence_no_  IN NUMBER,
   version_no_   IN NUMBER )
IS
   attr_       VARCHAR2(2000) := NULL;
   newrec_     CUSTOMER_AGREEMENT_TAB%ROWTYPE;
   oldrec_     CUSTOMER_AGREEMENT_TAB%ROWTYPE;
   objid_      CUSTOMER_AGREEMENT.objid%TYPE;
   objversion_ CUSTOMER_AGREEMENT.objversion%TYPE;
   indrec_     Indicator_Rec;
BEGIN
   Get_Id_Version_By_Keys___(objid_, objversion_, agreement_id_);
   oldrec_ := Lock_By_Id___(objid_, objversion_);
   newrec_ := oldrec_;
   Client_SYS.Add_To_Attr('MSG_SEQUENCE_NO', sequence_no_, attr_);
   Client_SYS.Add_To_Attr('MSG_VERSION_NO', version_no_, attr_);
   Unpack___(newrec_, indrec_, attr_);
   Check_Update___(oldrec_, newrec_, indrec_, attr_);
   Update___(objid_, oldrec_, newrec_, attr_, objversion_);
END Set_Msg_Sequence_And_Version;

✔BLOBDataType (WIP)

@IgnoreUnitTest BLOBDataType
@Override
@IgnoreUnitTest BLOBDataType
PROCEDURE test (

IS
BEGIN

END test;

MethodOverriding (WIP)

@IgnoreUnitTest MethodOverriding

Once we override a Framework generated method, which calls a super() method, there is no point in us writing a separate Unit Test for that Framework generated method. Then it is advisable to use this annotation.

But there could some other scenarios where the annually overridden code in such a method might need test support.

In such situations, it is ideal to move the manually written code if possible for a small testable method, and annotate the Overridden method.

Sample method (ORDER/CustomerOrderLine.plsql)

@IgnoreUnitTest MethodOverride
@Override
PROCEDURE Unpack___ (
   newrec_   IN OUT customer_order_line_tab%ROWTYPE,
   indrec_   IN OUT Indicator_Rec,
   attr_     IN OUT VARCHAR2 )
IS
BEGIN
   IF (newrec_.rowstate IS NULL) THEN
      Pre_Unpack_Insert___(attr_, newrec_);
   ELSE
      Pre_Unpack_Update___(attr_, newrec_);
   END IF;
   --Add pre-processing code here
   super(newrec_, indrec_, attr_);
   --Add post-processing code here
END Unpack___;

PipelinedFunction

@IgnoreUnitTest PipelinedFunction

Sample method (ORDER/TaxHandlingOrderUtil.plsql)

@IgnoreUnitTest PipelinedFunction
@UncheckedAccess
FUNCTION Get_Amounts(
   net_curr_amount_        IN NUMBER,
   gross_curr_amount_      IN NUMBER,
   company_                IN VARCHAR2,
   source_ref_type_        IN VARCHAR2,
   source_ref1_            IN VARCHAR2,
   source_ref2_            IN VARCHAR2,
   source_ref3_            IN VARCHAR2,
   source_ref4_            IN VARCHAR2,
   source_ref5_            IN VARCHAR2) RETURN Amounts_Arr PIPELINED
IS
   rec_                    Amounts_Rec;
   line_tax_curr_amount_   NUMBER;
   line_tax_dom_amount_    NUMBER;
   line_net_dom_amount_    NUMBER;
   line_gross_dom_amount_  NUMBER;
   line_net_curr_amount_   NUMBER := net_curr_amount_;
   line_gross_curr_amount_ NUMBER := gross_curr_amount_;
BEGIN
   Get_Amounts(line_tax_dom_amount_,
               line_net_dom_amount_,
               line_gross_dom_amoun_,
               line_tax_curr_amount_,
               line_net_curr_amount_,
               line_gross_curr_amount_,
               company_,
               source_ref_type_,
               source_ref1_,
               source_ref2_,
               source_ref3_,
               source_ref4_,
               source_ref5_);
   rec_.tax_curr_amount   := line_tax_curr_amount_;
   rec_.net_curr_amount    := line_net_curr_amount_;
   rec_.gross_curr_amount := line_gross_curr_amount_;
   rec_.tax_dom_amount  := line_tax_dom_amount_
   rec_.net_dom_amount   := line_net_dom_amount_
   rec_.gross_dom_amount   := line_gross_dom_amount_;
   PIPE ROW(rec_);
END Get_Amounts;

NoOutParams

@IgnoreUnitTest NoOutParams

Without the OUT parameters, it would be difficult to write the asserts. But on some methods, you can add asserts inside mock methods and accomplish a Valid Usable test case.

Sample method (ORDER/CustomerOrderLine.plsql)

@IgnoreUnitTest NoOutParams
-- Check_Allow_Backorders___
--   Check allowance of backorders
PROCEDURE Check_Allow_Backorders___ (
   order_no_              IN VARCHAR2,
   line_no_               IN VARCHAR2,
   rel_no_                IN VARCHAR2,
   line_item_no_          IN NUMBER,
   planned_delivery_date_ IN DATE )
IS
   found_  NUMBER;
   CURSOR get_rec IS
      SELECT 1
      FROM   CUSTOMER_ORDER_LINE_TAB
      WHERE  order_no = order_no_
      AND    planned_delivery_date != planned_delivery_date_
      AND    (line_no != line_no_ OR rel_no != rel_no_ OR line_item_no != line_item_no_);
BEGIN
   IF (CUSTOMER_ORDER_API.Get_Backorder_Option_Db(order_no_) = 'NO PARTIAL DELIVERIES ALLOWED') THEN
      OPEN get_rec;
      FETCH get_rec INTO found_;
      IF get_rec%FOUND THEN
         Client_SYS.Add_Info(lu_name_, 'SAME_DATE: The planned delivery dates should be the same when no backorders are allowed.');
      END IF;
      CLOSE get_rec;
   END IF;
END Check_Allow_Backorders___;

PLSQLInSQL

@IgnoreUnitTest PLSQLInSQL

Sample method (ORDER/CustomerOrderLine.plsql)

@IgnoreUnitTest PLSQLInSQL
-- gelr:delivery_types_in_pbi, begin
-- Get_Amt_Ratio_Per_Tax_Deliv___
--   Get the CO line gross amount ratio per tax code and Delivery Type. If the Delivery Type is null It will be calculated per Tax Code
FUNCTION Get_Amt_Ratio_Per_Tax_Deliv___ (
   order_no_      IN VARCHAR2,
   tax_code_      IN VARCHAR2,
   delivery_type_ IN VARCHAR2 DEFAULT NULL) RETURN NUMBER
IS
   gross_amount_ratio_     NUMBER;
   line_count_             NUMBER;
   rec_count_              NUMBER;
   ratio_sum_              NUMBER;
   rounding_               NUMBER;
   ordrec_                 Customer_Order_API.Public_Rec;
   ord_gross_amount_       NUMBER;
   string_null_            VARCHAR2(15) := Database_SYS.string_null_;
   CURSOR get_total_lines IS
      SELECT COUNT(COUNT(tax_code))
        FROM PREPAY_TAX_INFO
       WHERE order_no = order_no_
    GROUP BY tax_code, delivery_type;
   CURSOR get_gross_amount IS
      SELECT tax_code,
             delivery_type,
             (SUM(Get_Sale_Price_Total(order_no, line_no, rel_no, line_item_no)) +
              SUM(Get_Total_Tax_Amount_Curr(order_no, line_no, rel_no, line_item_no))) gross_amount
      FROM PREPAY_TAX_INFO
      WHERE order_no = order_no_
      GROUP BY tax_code, delivery_type
      ORDER BY tax_code, delivery_type;
BEGIN
   OPEN get_total_lines;
   FETCH get_total_lines INTO line_count_;
   CLOSE get_total_lines;
   ordrec_           := Customer_Order_API.Get(order_no_);
   rounding_         := Currency_Code_API.Get_Currency_Rounding(Site_API.Get_Company(ordrec_.contract), ordrec_.currency_code);
   rec_count_        := 0;
   ratio_sum_        := 0;
   ord_gross_amount_ := Customer_Order_API.Get_Ord_Gross_Amount(order_no_);
   FOR rec_ IN get_gross_amount LOOP
      rec_count_ := rec_count_ + 1;
      -- Comparison of delivery_type was added to the condition
      IF ((rec_.tax_code = tax_code_) AND 
          NVL(rec_.delivery_type, string_null_) = NVL(delivery_type_, string_null_)) THEN
         IF (rec_count_ != line_count_) THEN
            gross_amount_ratio_ := rec_.gross_amount / ord_gross_amount_;
            RETURN gross_amount_ratio_;
         ELSE
            -- This is to avoid miscalculation of rounding error.
            gross_amount_ratio_ := (1 - ratio_sum_);
            RETURN gross_amount_ratio_;
         END IF;
      END IF;
      ratio_sum_ := ratio_sum_ + (rec_.gross_amount / ord_gross_amount_);
   END LOOP;
END Get_Amt_Ratio_Per_Tax_Deliv___;

DynamicStatement

@IgnoreUnitTest DynamicStatement

When a method contains a dynamic statement, that method is not possible to unit test. In such cases, developer can use DynamicStatement annotation to ignore the method from unit testing.

Sample method (ORDER/TaxHandlingOrderUtil.plsql)

@IgnoreUnitTest DynamicStatement
PROCEDURE Get_External_Tax_Info___ (
   attr_            OUT VARCHAR2,
   source_ref1_     IN VARCHAR2,
   source_ref2_     IN VARCHAR2,
   source_ref3_     IN VARCHAR2,
   source_ref4_     IN VARCHAR2,
   source_ref_type_ IN VARCHAR2,
   company_         IN VARCHAR2)
IS
   source_pkg_                   VARCHAR2(30);
   stmt_                         VARCHAR2(2000);    
BEGIN
   source_pkg_  := Get_Source_Pkg___(source_ref_type_);
   Assert_Sys.Assert_Is_Package(source_pkg_);
   -- Get the line original qty
   stmt_  := 'BEGIN
                 '||source_pkg_||'.Get_External_Tax_Info(:attr, :rec_source_ref1, :rec_source_ref2, :rec_source_ref3, :rec_source_ref4, :company);
              END;';
   @ApproveDynamicStatement(2021-06-02, NiDalk) 
   EXECUTE IMMEDIATE stmt_
     USING OUT attr_,
           IN  source_ref1_,
           IN  source_ref2_,
           IN  source_ref3_,
           IN  source_ref4_,
           IN  company_; 
END Get_External_Tax_Info___;

TrivialFunction

@IgnoreUnitTest TrivialFunction

If the method is simple and straight forward there’s no point in adding an overhead to the test scripts by adding a test case to that method. Unit Test should be done for what it's intended to be used for and that is test isolated functionality, but not trivial functionality. Also always consider if it's worth creating the test.

Sample method 1 (ORDER/CustomerOrderLine.plsql)

@IgnoreUnitTest TrivialFunction
-- gelr:delivery_types_in_pbi, begin
-- Get_Gross_Amnt_Ratio_Per_Tax__
-- Get the CO line gross amount ratio per tax code and delivery type
FUNCTION Get_Amt_Ratio_Per_Tax_Deliv__ (
   order_no_      IN VARCHAR2,
   tax_code_      IN VARCHAR2,
   delivery_type_ IN VARCHAR2 DEFAULT NULL) RETURN NUMBER
IS
BEGIN
   RETURN Get_Amt_Ratio_Per_Tax_Deliv___(order_no_, tax_code_, delivery_type_);
END Get_Amt_Ratio_Per_Tax_Deliv__;
-- gelr:delivery_types_in_pbi, end

Sample method 2 (ORDER/CustomerOrderLine.plsql)

@IgnoreUnitTest TrivialFunction
-- Get_Objversion
--   Return the current objversion for line.
@UncheckedAccess
FUNCTION Get_Objversion (
   order_no_     IN VARCHAR2,
   line_no_      IN VARCHAR2,
   rel_no_       IN VARCHAR2,
   line_item_no_ IN NUMBER ) RETURN VARCHAR2
IS
   temp_  CUSTOMER_ORDER_LINE_TAB.rowversion%TYPE;
   CURSOR get_attr IS
      SELECT rowversion
      FROM   CUSTOMER_ORDER_LINE_TAB
      WHERE  order_no = order_no_
      AND    line_no = line_no_
      AND    rel_no = rel_no_
      AND    line_item_no = line_item_no_;
BEGIN
   OPEN get_attr;
   FETCH get_attr INTO temp_;
   CLOSE get_attr;
   RETURN TO_CHAR(temp_, 'YYYYMMDDHH24MISS');
END Get_Objversion;