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