Efficiently using Oracle sequences which have increment set greater than 1

Some application's create sequences which auto increment by say 200, Map 3d does this for example with the ENTITYID sequence. It means Map 3d reads the sequence once and then waits until it has used 199 values before grabbing another sequence.

This can be inefficient if you loading data via a INSERT into SELECT approach, each row will up the sequence by 200, not really what your after.

Oracle's PL/SQL functions to the rescue

CREATE SEQUENCE SEQ
  START WITH 1
  INCREMENT BY 200
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;
  
CREATE OR REPLACE FUNCTION getseq( rn integer, incred integer )
RETURN number IS
calc_seq number;
BEGIN
  IF mod(rn, incred) = 0 OR rn = 1 THEN
    select seq.nextval into calc_seq from dual;
     RETURN calc_seq ;
  ELSE
    select seq.currval  into calc_seq from dual;
     RETURN calc_seq + mod(rn, incred);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
     RETURN -1;
END;
/

-- populate currval for the session so the next query doesn't error

SELECT  SEQ.nextval from dual; 

-- handy way to get 30 rows from dual, using 10 rather than 200 to keep it simple  

SELECT  rownum, zac.getseq(rownum,10) seq, mod(rownum,10) mod_result, seq.currval 
FROM dual connect by rownum < 31;


    ROWNUM        SEQ MOD_RESULT CURRVAL
---------- ---------- ---------- -------
         1        201          1     200
         2        203          2     200
         3        204          3     200
         4        205          4     200
         5        206          5     200
         6        207          6     200
         7        208          7     200
         8        209          8     200
         9        210          9     200
        10        401          0     400
        11        402          1     400
        12        403          2     400
        13        404          3     400
        14        405          4     400
        15        406          5     400
        16        407          6     400
        17        408          7     400
        18        409          8     400
        19        410          9     400
        20        601          0     600
        21        602          1     600
        22        603          2     600
        23        604          3     600
        24        605          4     600
        25        606          5     600
        26        607          6     600
        27        608          7     600
        28        609          8     600
        29        610          9     600
        30        801          0     800
30 rows selected