Oracle PL/SQL for DBAs

Finding Genuine Repeats

Finding genuine repeats is straightforward. If the order type matches the repeat type of a criteria record, and if a corresponding match is found in the associative array containing potential repeats, then we have found a genuine repeat. I’ve put this code into a sub-function, as well.

    /*------------------------------------------------------------------*/
    FUNCTION order_is_a_repeat ( p_location_id NUMBER,
                                 p_type_id     NUMBER,
                                 p_date        DATE )
             RETURN NUMBER IS
    /*------------------------------------------------------------------*/
      v_hash NUMBER;
    BEGIN

      -- for every criteria...
      FOR counter IN 1..v_criteria.LAST LOOP

        -- if order type matches the repeat order type of a criteria
        IF v_criteria(counter).repeat_type_id = p_type_id THEN

          -- calculate a hash of the location, first and repeat order types
          v_hash := DBMS_UTILITY.GET_HASH_VALUE(p_location_id || ':' ||
                            v_criteria(counter).first_type_id || ':' ||
                            v_criteria(counter).repeat_type_id,
                            -32767,65533);

          -- if logged as a potential repeat then its safe to assume
          -- we are repeating now
          IF v_potential_repeat.EXISTS(v_hash) THEN
            RETURN(v_hash);
          END IF;

        END IF;  -- order type match

      END LOOP;  -- every criteria

      RETURN(NULL);

    END order_is_a_repeat;

The algorithm is as follows. For every criterion for the region:

• Does the order type of the order being processed match the repeat-order type of the criterion? If yes, then carry on.

• Manufacture a hash value based on the location of the order, the first order type of the criterion, and ...