Create a Dutch BSN generator in Oracle (Dutch)
BSN = Burger Service Nummer.
Below an example of an Oracle PL/SQL function to generate a valid Dutch BSN Code "new_bsn"
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_bsn()
Step-by-step guide
create sequence seq_bsn start with 22345678 increment by 1; -- These are the first 8 positions.
CREATE OR REPLACE function new_bsn
return number
is
som number ( 10, 0 ) ;
bsnbegin number ( 10, 0 ) ;
bsn number ( 10, 0 ) ;
ltste_get number ( 10, 0 ) ;
begin
/* Start loop to get valid 11-proof number */
loop
/* New sequence in every loop cycle */
select seq_bsn.nextval into bsnbegin from dual;
/* Extract the sequence number (8 positions) Multiply this using the 11-proof manner */
som := ( ( substr ( bsnbegin, 0, 1 ) * 9 ) + ( substr ( bsnbegin, 2, 1 ) * 8 ) + ( substr ( bsnbegin, 3, 1 ) * 7 ) + ( substr ( bsnbegin, 4, 1 ) * 6 ) + ( substr ( bsnbegin, 5, 1 ) * 5 ) + ( substr ( bsnbegin, 6, 1 ) * 4 ) + ( substr ( bsnbegin, 7, 1 ) * 3 ) + ( substr ( bsnbegin, 8, 1 ) * 2 ) ) ;
/* calculate the alst number (9th); (i1*9 + i2*8 +..+ i9*1) it Should be divisible by 11; so i9 = 11-mod(..,11) */
ltste_get := mod(som,11);
/* bsn to display */
bsn := bsnbegin||ltste_get;
exit
/* check the loop; ltste_get must be smaller then 10 */
when length ( ltste_get ) = 1;
end loop;
/* Return bsn */
return bsn;
end;
You can run this function manually: select new_bsn() from dual;
You can use this function in your privacy expression: new_bsn()