Create an IBAN generator in Oracle (Dutch)

Below an example of an Oracle PL/SQL function to generate a valid Dutch IBAN Code "new_iban"

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

Step-by-step guide

SQL

create sequence seq_banknumber start with 12345678 increment by 1; -- These are the first 8 positions.
  
  
CREATE OR REPLACE function new_iban (p_bnkcode char default 'INGB', p_bnkcountry char DEFAULT 'NL')
    return varchar2
  is
    som       number ( 10, 0 ) ;
    bankbegin number ( 20, 0 ) ;
    bankaccnr number ( 38, 0 ) ;
    ltste_get number ( 10, 0 ) ;
    iban varchar2(4000);
      
    bnkcode varchar2(4) := p_bnkcode;
    bnkcountry varchar2(2) := p_bnkcountry;
           
    land_c1 varchar(5) := substr(bnkcountry,1,1); -- 1e land character
    land_c2 varchar(5):= substr(bnkcountry,2,1); -- 2e land character
    land_p1 number(5):= ascii(land_c1)-55; -- A wordt 10, B wordt 11, ...
    land_p2 number(5):= ascii(land_c2)-55; -- A wordt 10, B wordt 11, ...
       
    bankcode_c1 varchar(5) := substr(bnkcode,1,1); -- 1e land character
    bankcode_c2 varchar(5):= substr(bnkcode,2,1); -- 2e land character
    bankcode_c3 varchar(5):= substr(bnkcode,3,1); -- 2e land character
    bankcode_c4 varchar(5):= substr(bnkcode,4,1); -- 2e land character
    bankcode_p1 number(5):= ascii(bankcode_c1)-55; -- A wordt 10, B wordt 11, ...
    bankcode_p2 number(5):= ascii(bankcode_c2)-55; -- A wordt 10, B wordt 11, ...
    bankcode_p3 number(5):= ascii(bankcode_c3)-55; -- A wordt 10, B wordt 11, ...
    bankcode_p4 number(5):= ascii(bankcode_c4)-55; -- A wordt 10, B wordt 11, ...
       
    control_code varchar(2);
       
  begin
    /* Start loop to get valid 11-proof number */
    loop
      /* New sequence in every loop cycle */
      select seq_banknumber.nextval into bankbegin from dual;
      
      /* Extract the sequence number  (8 positions) Multiply this using the 11-proof manner */
      som := ( ( substr ( bankbegin, 0, 1 ) * 9 ) + ( substr ( bankbegin, 2, 1 ) * 8 ) + ( substr ( bankbegin, 3, 1 ) * 7 ) + ( substr ( bankbegin, 4, 1 ) * 6 ) + ( substr ( bankbegin, 5, 1 ) * 5 ) + ( substr ( bankbegin, 6, 1 ) * 4 ) + ( substr ( bankbegin, 7, 1 ) * 3 ) + ( substr ( bankbegin, 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 := 11-mod(som,11);
      /* bankaccountnumber to display */
      bankaccnr := bankbegin||ltste_get;
      exit
      /* check the loop; ltste_get must be smaller then  10  */
    when ltste_get < 10;
    end loop;
    /* Return the bankaccountnumber */
       
    /* Calc control number */
    control_code := lpad(to_char(98- mod(to_number(bankcode_p1||bankcode_p2||bankcode_p3||bankcode_p4||lpad(bankaccnr,10,'0')||land_p1||land_p2||'00'),97)),2,'0')  ;
       
    iban := bnkcountry||control_code||' '||bnkcode||' '||lpad(bankaccnr,10,'0');
       
    return iban;
       
  end;

You can run this function manually.

select new_iban() from dual;

Result: NL32 INGB 0124600212

By default the generated number results in the bank 'INGB'. You can provide your own bank code.

select new_iban('ASNB') from dual;

Result: NL67 ASNB 0124600239

You can use this function in your privacy expression: new_iban() or new_iban('ASNB')