Test Program for the Oracle MOD Formula

The Oracle package below contains the test code and the formulas to test. After creating the package, type the following to run it from SQL*Plus (the SIZE in SET SERVEROUTPUT has to be at least 2000):

SET SERVEROUTPUT ON SIZE 2000
EXEC TestModFormula.TestMain

And here’s the package code:

CREATE OR REPLACE PACKAGE TestModFormula AS
 PROCEDURE TestMain;
 FUNCTION EdMod(dividend NUMBER, divisor NUMBER) RETURN NUMBER;
 FUNCTION TechOnNetMod(dividend NUMBER, divisor NUMBER) RETURN NUMBER;
END TestModFormula;
/

CREATE OR REPLACE PACKAGE BODY TestModFormula AS
  PROCEDURE TestMain IS
    dividendCounter NUMBER;
    dividend NUMBER;
    divisorCounter NUMBER;
    divisor NUMBER;
    oracleMod NUMBER;
    edModValue NUMBER;
    edModErrors NUMBER := 0;
    techOnNetModValue NUMBER := 0;
    techOnNetModErrors NUMBER := 0;
  BEGIN
    FOR dividendCounter IN -1000..1000 LOOP
      dividend := dividendCounter * 9.4;
      FOR divisorCounter IN -2500..2500 LOOP
        divisor := divisorCounter / 10;
        -- Get the Oracle modulus
        oracleMod := MOD(dividend, divisor);
        -- Get the modulus using the logic I think Oracle uses
        -- and count all the wrong answers.
        edModValue := EdMod(dividend, divisor);
        IF oracleMod <> edModValue THEN
          edModErrors := edModErrors + 1;
        END IF;
        -- Get the modulus using TechOnTheNet's published logic
        -- and count all the wrong answers.
        BEGIN
          techOnNetModValue := TechOnNetMod(dividend, divisor);
          IF oracleMod <> techOnNetModValue THEN
            techOnNetModErrors := techOnNetModErrors + 1;
          END IF;
        EXCEPTION
          -- TechOnTheNet formula will fail when divisor is zero;
          -- just count it as a bad result like any other.
          WHEN OTHERS THEN techOnNetModErrors := techOnNetModErrors + 1;
        END;
      END LOOP; -- divisorCounter
    END LOOP; -- dividendCounter
    DBMS_Output.Put_Line('Number of times my MOD differed from Oracle''s: ' || edModErrors);
    DBMS_Output.Put_Line('Number of times TechOnTheNet''s MOD differed: ' || techOnNetModErrors);
  END TestMain;

  FUNCTION EdMod(dividend NUMBER, divisor NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN COALESCE(SIGN(dividend) * (ABS(dividend) - FLOOR(ABS(dividend / NULLIF(divisor, 0))) * ABS(divisor)), dividend);
  END EdMod;
  
  FUNCTION TechOnNetMod(dividend NUMBER, divisor NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN dividend - divisor * FLOOR(dividend / divisor);
  END TechOnNetMod;

END TestModFormula;
/

Leave a Reply

Your email address will not be published. Required fields are marked *