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
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()