Create a GBA generator in Oracle (Dutch)

GBA= Gemeentelijke Basis Adminstratie.

Below an example of an Oracle PL/SQL function to generate a valid Dutch GBA number "new_gba"

This function can be used in Privacy as an expression.

To create this function you require the following permissions:

CREATE SEQUENCE
CREATE FUNCTION

To use these function you require:

EXECUTE permission on the function new_gba()

Step-by-step guide

SQL

CREATE SEQUENCE "SEQ_GBA" MAXVALUE 999999999 INCREMENT BY 1 START WITH 135792468;
/
create or replace function new_gba
  return number
is
gbanr number ( 10, 0 ) ;
commit_cnt number ( 10, 0 ) ;
first_digit number ( 10, 0 ) ;
checksum number ( 10, 0 ) ;
begin
  select seq_gba.nextval into anr from dual;
  loop
    gbanr := gbanr + 1;
    -- if 2 neighbor digits are equal, then raise right digit
    if substr(gbanr,1,1)=substr(gbanr,2,1) then gbanr := gbanr + 10000000; end if;
    if substr(gbanr,2,1)=substr(gbanr,3,1) then gbanr := gbanr + 1000000 ; end if;
    if substr(gbanr,3,1)=substr(gbanr,4,1) then gbanr := gbanr + 100000 ; end if;
    if substr(gbanr,4,1)=substr(gbanr,5,1) then gbanr := gbanr + 10000 ; end if;
    if substr(gbanr,5,1)=substr(gbanr,6,1) then gbanr := gbanr + 1000 ; end if;
    if substr(gbanr,6,1)=substr(gbanr,7,1) then gbanr := gbanr + 100 ; end if;
    if substr(gbanr,7,1)=substr(gbanr,8,1) then gbanr := gbanr + 10 ; end if;
    if substr(gbanr,8,1)=substr(gbanr,9,1) then gbanr := gbanr + 1 ; end if;
    -- determine first digit
    first_digit := 11-mod(
    + 2 *  substr(gbanr,1,1)
    + 4 *  substr(gbanr,2,1)
    + 8 *  substr(gbanr,3,1)
    + 16 * substr(gbanr,4,1)
    + 32 * substr(gbanr,5,1)
    + 64 * substr(gbanr,6,1)
    + 128* substr(gbanr,7,1)
    + 256* substr(gbanr,8,1)
    + 512* substr(gbanr,9,1),11);
    exit when (first_digit != substr(gbanr,1,1) and first_digit <> 0 and first_digit <= 9); -- first digit <> 0 and first_digit <> firstdigit of gbanr
  end loop;
  /* gbanr teruggeven */
  return gbanr;
end;

You can run this function manually.

select new_gba() from dual;

You can use this function in your privacy expression:

new_gba()