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

SQL

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