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;
``````
SQL

You can run this function manually: select new_bsn() from dual;

You can use this function in your privacy expression: new_bsn() 