Here is a PL/SQL package that I’ve made to check and convert Belgian’s BBANs and IBANs codes whitin SQL queries.
package : BBAN_IBAN_BIC.sql (157)
package body : BBAN_IBAN_BIC_Body.sql (153)
You’ll need a table of BICs codes to get the BIC code out of BBAN and IBAN.
Download an updated version from :
http://www.bnb.be/ « Systèmes de paiement »> »Standards de paiements »> »Codes d’identification des banques »
or you could use directly this one (at youre own risk) : BIC_Table.sql (243)
Here is a package containing functions to interact with delimited strings.
Download STR package (204)the package is named STR and has to be placed in the SYS schema to be used anywhere in the database. Just compile it as sys then make a public synonym of it. Then you will be able to use functions this way:
select str.<thefunction>(<parameters>) from dual;
Here is a couple of cool stuffs that could be realized with this package (for full details download the package)
—————————————————————————————————–
Function will split a string. The maximum size returnable
is 4000 bytes and up to 15 fields.
For Exammple:
SQL> select str.split(’a,b,c,d’,3,’,') from dual;
STR.SPLIT(’A,B,C,D’,3,’,')
——————————————————————————–
c
select str.split(’a,b,c,d’,3,’,') from dual;
@param in_del_field string to be split
@param in_position position of split character(s) to return
@param in_del delimter to split by
@return Returns a single value from a string, up to 4K long
FUNCTION split
(in_del_field IN VARCHAR2,
in_pos IN NUMBER,
in_del IN VARCHAR2)
RETURN VARCHAR2;
Type is a collection/array of values returned from the split_array function
TYPE tab_split IS TABLE OF VARCHAR2(4000);
—————————————————————————————————–
Funciton takes a string passed to it and returns it as a collection of the
type tab_split. For Example
DECLARE
t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
i INTEGER;
BEGIN
FOR i IN t_split.FIRST .. t_split.LAST LOOP
DBMS_OUTPUT.PUT_LINE(t_split(i));
END LOOP;
END;
@param in_del_field string to be split
@param in_del delimter to split by
@return Returns an Array of type str.tab_split with a row for each delimited value in in_del_field.
FUNCTION split_array
(in_del_field IN VARCHAR2,
in_del IN VARCHAR2)
RETURN tab_split;
—————————————————————————————————–
Funciton takes a table of string passed to it and returns it as a delimited string. For Example
DECLARE
t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
i INTEGER;
BEGIN
FOR i IN t_split.FIRST .. t_split.LAST LOOP
DBMS_OUTPUT.PUT_LINE(t_split(i));
END LOOP;
END;
@param in_tab table to unsplit
@param in_del delimter to concat
@return Returns an Varchar2 string delimited by the in_del var passed.
FUNCTION unsplit_array
(in_tab IN tab_split,
in_del IN VARCHAR2)
RETURN VARCHAR2;
—————————————————————————————————–
Function replace a string in a delimited string at the position passed in parameter. For Example
select str.replace_at_pos(’a,b,c,d,e,f,g,h,i’,2,’z',’,') from dual;
/
a,z,c,d,e,f,g,h,i
@param in_del_field delimited string
@param in_pos position where to replace string
@param in_text string to put in place at pos
@param in_del delimter to split by
@return Returns an Varchar2 string delimited by the in_del var passed.
FUNCTION replace_at_pos
(in_del_field IN VARCHAR2,
in_pos IN NUMBER,
in_text IN VARCHAR2,
in_del IN VARCHAR2)
RETURN VARCHAR2;
—————————————————————————————————–
Pipelined Function returns a table of one column with the deleminted string
passed to it. For example, you could do the following to return a table of
the alphabet…
SELECT *
FROM TABLE(str.split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',','))
@param in_del_field string to be split
@param in_del delimter to split by
@return Returns a Piplined Table of Table Type tab_str with a row for each delimited value in in_del_field.
FUNCTION split_pipe
(in_del_field IN VARCHAR2,
in_del IN VARCHAR2)
RETURN tab_str PIPELINED;
download the package Download STR package (204)