Oracle Integration Manual

Oracle Integration Guide

Configure Oracle for Postcode Plus

Install Postcode Plus on your Oracle Server then copy the pcporacle.so (Unix) or pcporacle.dll (Windows) to an appropriate location on the same server. Although the library can be placed anywhere, a suitable location would be the lib subfolder of your Oracle home path.

Ensure your listener.ora and tnsnames.ora in the network\admin subfolder of your Oracle home path are correctly configured to use this external process.

Your listener.ora file should be similar to the following:

				
					# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
            )
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 1521))
            )
        )
    )
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\ora92)
            (PROGRAM = extproc)
            (ENVS="EXTPROC_DLLS=ANY") 
        )
        (SID_DESC =
            (GLOBAL_DBNAME = name.domain)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = name)
        )
        (SID_DESC =
            (GLOBAL_DBNAME = Test)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = Test)
        )
    )

You should see something similar to this in your own file. Generally this will all be set up fine for you by Oracle. Your database names and paths will obviously be different in your own configuration file. The important thing to check is that in the SID_LIST_LISTENER section you have a SID_DESC to run external procedures similar to the first entry in that list above. You will need to add to that section a line similar to:

				
					(ENVS="EXTPROC_DLLS=ANY")

To allow the DLL to run if it is not in your Oracle bin or lib folder. Using “ANY” is useful for testing, but for additional security you may prefer to use a line like:

				
					(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\pcporacle.dll")

Please note that you can colon separate entries in that line if you already have another DLL listed, e.g.

				
					(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\pcporacle.dll:c:\inhouse\myinhouse.dll")

Your tnsnames.ora file along with details of your database names also needs to include details for the extproc listener as well, similar to the following:

				
					EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
            (CONNECT_DATA =
                (SID = PLSExtProc)
                (PRESENTATION = RO)
            )
        )

Again this should have been configured for you when you installed Oracle.

If you need further assistance configuring Oracle to use our DLL then please email Support. If your query relates to more complex issues regarding Oracle database configuration generally then please contact your normal database support provider.

Add the Functions

To declare all the functions in the pcpOracle.dll to enable you to use all the functionality of AFD Postcode Plus through SQL statements or stored procedures you will need to add the following Functions and Procedures to your database. The easiest way to do this is to launch SQL Plus on your Oracle server and enter the following statements. You can enter and/or manage these how you wish of course.

Windows:

create library pcplus as ‘c:\oracle\ora92\lib\pcporacle.dll’;

Unix:

create library pcplus as ‘/opt/Oracle/OraHome/lib/pcporacle.so’;

				
					CREATE OR REPLACE FUNCTION GetPostcode(
pcode varchar2, recNo BINARY_INTEGER, dpts OUT BINARY_INTEGER, changeFlag OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetPostcode" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
pcode string, recNo long, dpts long, changeFlag long);
/
CREATE OR REPLACE FUNCTION GetAddress(recNo BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetAddress" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);
/
CREATE OR REPLACE FUNCTION FastFind(findStr varchar2, changeFlag OUT BINARY_INTEGER, oldPcode OUT varchar2, newPcode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoFastFind" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (findStr string, changeFlag long, oldPcode string, newPcode string); 
/
CREATE OR REPLACE FUNCTION InitiateSearch(pcFrom varchar2, pcTo varchar2, stdCode varchar2, org varchar2, building varchar2, depStreet varchar2, street varchar2, dblDepLoc varchar2, depLoc varchar2, town varchar2, county varchar2, justBuilt varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoSearch2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (pcFrom string, pcTo string, stdCode string, org string, building string, depStreet string, street string, dblDepLoc string, depLoc string, town string, county string, justBuilt string);
/
CREATE OR REPLACE FUNCTION GetMatch (dpts OUT BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetMatch" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (dpts long);
/
CREATE OR REPLACE PROCEDURE GetLocality(
pcode OUT varchar2, depStreet OUT varchar2, street OUT varchar2, dblDepLoc OUT varchar2, depLoc OUT varchar2, town OUT varchar2, countySource OUT varchar2, county OUT varchar2, countyAbbr OUT varchar2, mailsort OUT varchar2, stdCode OUT varchar2, gridE OUT varchar2, gridN OUT varchar2, wardCode OUT varchar2, wardName OUT varchar2, nhsCode OUT varchar2, nhsName OUT varchar2, nhsRegionCode OUT varchar2, nhsRegion OUT varchar2, deliveryPoints OUT varchar2, userType OUT varchar2, constituency OUT varchar2, tvRegion OUT varchar2, authority OUT varchar2, tradCounty OUT varchar2, adminCounty OUT varchar2, censusCode OUT varchar2, affluence OUT varchar2, lifeStage OUT varchar2, additionalCensusInfo OUT varchar2, occupancy OUT varchar2, addressType OUT varchar2, pctCode OUT varchar2, pctName OUT varchar2, eerCode OUT varchar2, eerName OUT varchar2, urbanRuralCode OUT varchar2, urbanRuralName OUT varchar2, leaCode OUT varchar2, leaName OUT varchar2, latitude OUT varchar2, longitude OUT varchar2)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetLocRec4"
LANGUAGE C
PARAMETERS (pcode string, depStreet string, street string, dblDepLoc string, depLoc string,
town string, countySource string, county string, countyAbbr string, mailsort string,
stdCode string, gridE string, gridN string, wardCode string, wardName string,
nhsCode string, nhsName string, nhsRegionCode string, nhsRegion string,
deliveryPoints string, userType string, constituency string, tvRegion string,
authority string, tradCounty string, adminCounty string, censusCode string, affluence string, lifeStage string, additionalCensusInfo string, occupancy string, addressType string, pctCode string, pctName string, eerCode string, eerName string, urbanRuralCode string, urbanRuralName string, leaCode string, leaName string, latitude String, longitude string);
/
CREATE OR REPLACE PROCEDURE GetFormattedAddress(
organisation OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2,
town OUT varchar2, county OUT varchar2, pcode OUT varchar2, countyType BINARY_INTEGER)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetFormattedAddress"
LANGUAGE C
PARAMETERS (organisation string, property string, street string, locality string, town string,
county string, pcode string, countyType long);
/
CREATE OR REPLACE PROCEDURE GetAddressLine(
line OUT varchar2, countyType BINARY_INTEGER)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetAddressLine"
LANGUAGE C
PARAMETERS (line string, countyType long);
/
CREATE OR REPLACE PROCEDURE GetAddressRec(
poBox OUT varchar2, organisation OUT varchar2, department out varchar2, number OUT varchar2,
subBuilding OUT varchar2, building OUT varchar2, dps OUT varchar2, households OUT varchar2, udprn OUT varchar2, justBuilt OUT varchar2)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetAddrRec2"
LANGUAGE C
PARAMETERS (poBox string, organisation string, department string, number string, subBuilding string,
building string, dps string, households string, udprn string, justBuilt string);
/
CREATE OR REPLACE FUNCTION LookupUdprn(udprn varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoLookupUdprn" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (udprn string);
/

Full details of each of these Function calls is given in the Function reference.

Using the API

Run the PCLookup function to check everything is working. You can then add the Postcode Plus functionality to your Oracle system using PL/SQL.

Postcode Plus Examples

PCLookup

To test you have correctly installed and configured Postcode Plus for Oracle on your server you can paste the following test procedure into SQL Plus to perform a simple Postcode lookup.

				
					CREATE OR REPLACE PROCEDURE PcLookup (postcodeIn varchar2) AS
  postcodeOut varchar2(255);
  line varchar2(512);
  countyType integer;
  retVal integer;
  locRec integer;
  locRecs integer;
  addrRec integer;
  dpts integer;
  changeFlag integer;
BEGIN
  countyType := 3;
  locRec := 1;
  locRecs := GetPostcode(postcodeIn, locRec, dpts, changeFlag);
  IF locRecs < 1 THEN
    IF locRecs < -2 THEN
      dbms_output.put_line(locRecs);
    ELSE
      dbms_output.put_line('Postcode Not Found');
    END IF;
  ELSE
    LOOP
      addrRec := 1;
      LOOP
        EXIT WHEN addrRec > dpts;
        retVal := GetAddress(addrRec);
        GetAddressLine(line, countyType);
        dbms_output.put_line(line);
        addrRec := addrRec + 1;
      END LOOP;
      locRec := locRec + 1;
      EXIT WHEN locRec > locRecs;
      retVal := GetPostcode(postcodeIn, locRec, dpts, changeFlag);
    END LOOP;
  END IF;
END;
/

Before executing this procedure, set serveroutput to on so that you will see it’s output, by entering the following line into SQL Plus:

				
					set serveroutput on;

Now to test a lookup execute the function supplying it with a valid postcode, for example:

				
					execute PcLookup('B11 1AA');

You will then see a list of all properties on that postcode.

See the function reference and the details of our Oracle Forms example for details of how the functions called in this procedure work and how to use them in your own database application.

Oracle Forms

You can use Postcode Plus for Oracle in Oracle Forms easily once you have installed and configured the library to run on your Oracle server. Code can be written to call the external procedures and the results can be assigned to on-screen controls for display or to table fields etc. simply by writing SQL in your Oracle Forms trigger methods.

You may find looking at the code contained in that form and the descriptions of the methods here invaluable in assisting in your integration of Oracle with Postcode Plus in other applications as well.

You can save the Oracle Form source code to try out in Oracle Forms Developer yourself from here. It demonstrates the use of a Fast Find, Postcode lookup and the search feature of Postcode Plus for Oracle. Simply load the pcplus.fmb file into Oracle Forms and connect to the database as a user which is configured to access the Postcode Plus for Oracle Procedures and Functions. You can then run the form as well as examine it’s code to use as a basis for your own integration.

The ‘Fast Find’ Button WHEN_MOUSE_CLICK Trigger

This trigger contains the following code:

				
					DECLARE
    fastFindStr varchar(255);
    changeFlag integer;
    pcodeFrom varchar(255);
    pcodeTo varchar(255);
    line varchar2(512);
    countyType integer;
    retVal integer;
    locRec integer;
    locRecs integer;
    addrRec integer;
    dpts integer;
    listElement Number;
    indexValue integer;
BEGIN
    countyType := 3;
    fastFindStr := :block4.find;
    locRecs := FastFind(fastFindStr, changeFlag, pcodeFrom, pcodeTo);
    CLEAR_LIST('results');
    listElement := 0;
    LOOP
        locRec := GetMatch(dpts);
        EXIT WHEN locRec < 0;
        addrRec := 1;
        IF locRec > 0 THEN
            LOOP 
                EXIT WHEN addrRec > dpts;
                retVal := GetAddress(addrRec);
                GetAddressLine(line, countyType);
                listElement := listElement + 1;
                indexValue := (locRec * 1000) + addrRec;
                Add_List_Element('results', listElement, line, listElement);
                Add_List_Element('resultindex', listElement, indexValue, listElement);
                addrRec := addrRec + 1;
            END LOOP;
        END IF;
    END LOOP;
END;

This trigger method queries Postcode Plus for the fast find string specified in the find Text Item. It retrieves all matching addresses and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item ‘resultsindex’ is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the ‘line’ variable can, of course, be assigned to any database field or control on the form as desired.

The ‘Lookup Postcode’ Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

				
					DECLARE
    postcode varchar(255);
    line varchar2(512);
    countyType integer;
    retVal integer;
    locRec integer;
    locRecs integer;
    addrRec integer;
    dpts integer;
    changeFlag integer;
    listElement Number;
    indexValue integer;
BEGIN
    countyType := 3;
    locRec := 1;
    postcode := :block4.find;
    locRecs := GetPostcode(postcode, locRec, dpts, changeFlag);
    CLEAR_LIST('results');
    IF locRecs < 1 THEN
        MESSAGE('Postcode Not Found', ACKNOWLEDGE);
    ELSE
        listElement := 0;
        LOOP
            addrRec := 1; 
            LOOP 
                EXIT WHEN addrRec > dpts;
                retVal := GetAddress(addrRec);
                IF retVal > 0 THEN
                   GetAddressLine(line, countyType);
                   listElement := listElement + 1;
                   indexValue := (locRec * 1000) + addrRec;
                   Add_List_Element('results', listElement, line, listElement);
                   Add_List_Element('resultindex', listElement, indexValue, listElement);
               END IF
               addrRec := addrRec + 1;
            END LOOP;
            locRec := locRec + 1;
            EXIT WHEN locRec > locRecs;
            retVal := GetPostcode(postcode, locRec, dpts, changeFlag);
        END LOOP;
    END IF; 
END;

This trigger method queries Postcode Plus for the postcode specified in the find Text Item. It retrieves all matching addresses and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item ‘resultsindex’ is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the ‘line’ variable can, of course, be assigned to any database field or control on the form as desired.

The Results List Item WHEN-LIST-CHANGED trigger retrieves and displays the full address details for the selected address.

The ‘Search’ Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

				
					DECLARE
    pcodeFrom varchar(255);
    pcodeTo varchar(255);
    stdCode varchar(255);
    organisation varchar(255);
    building varchar(255);
    depStreet varchar(255);
    street varchar(255);
    dblDepLoc varchar(255);
    depLoc varchar(255);
    town varchar(255);
    county varchar(255);
    justBuilt varchar(255);
    line varchar(512);
    countyType integer;
    retVal integer;
    locRec integer;
    locRecs integer;
    addrRec integer;
    dpts integer;
    listElement Number;
    indexValue integer;
    searchLimit integer;
    BEGIN
        countyType := 3;
        searchLimit := 1000; -- Set to stop Oracle going off retrieving infinite results with a vague search
        pcodeFrom:= :txtPostcode;
        if pcodeFrom IS NULL THEN pcodeFrom := ' '; END IF;
        pcodeTo := :txtPostcode;
        if pcodeTo IS NULL THEN pcodeTo := ' '; END IF;
        stdCode := ' ';
        organisation := :txtOrganisation;
        if organisation IS NULL THEN organisation := ' '; END IF;
        building := :txtProperty;
        if building IS NULL THEN building := ' '; END IF;
        depStreet := ' ';
        street := :txtStreet;
        if street IS NULL THEN street := ' '; END IF;
        dblDepLoc := ' ';
        depLoc := :txtLocality;
        if depLoc IS NULL THEN depLoc := ' '; END IF;
        town := :txtTown;
        if town IS NULL THEN town := ' '; END IF;
        county := :txtCounty;
        if county IS NULL THEN county := ' '; END IF;
        justBuilt := ' ';
        locRecs := InitiateSearch(pcodeFrom, pcodeTo, stdCode, organisation, building, depStreet, street, dblDepLoc, depLoc, town, county, justBuilt);
        CLEAR_LIST('results');
        listElement := 0;
        LOOP
            EXIT WHEN listElement > searchLimit;
            locRec := GetMatch(dpts);
            EXIT WHEN locRec < 0;
            addrRec := 1;
            IF locRec > 0 THEN
                LOOP 
                    EXIT WHEN addrRec > dpts;
                    EXIT WHEN listElement > searchLimit;
                    retVal := GetAddress(addrRec);
                    IF retVal > 0 THEN
                        GetAddressLine(line, countyType);
                        listElement := listElement + 1;
                        indexValue := (locRec * 1000) + addrRec;
                        Add_List_Element('results', listElement, line, listElement);
                        Add_List_Element('resultindex', listElement, indexValue, listElement);
                    END IF;
                    addrRec := addrRec + 1;
                END LOOP;
            END IF;
        END LOOP;
END;

This trigger method queries Postcode Plus for the search criteria specified in the text fields. The depStreet and dblDepLoc fields are the same for search purposes as street and depLoc respectively so the street and locality only need to be placed in one of those fields. In this case the Postcode is assigned to both the Postcode From and Postcode To fields if specified, but postcode range searching is also supported.

It retrieves all matching addresses and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item ‘resultsindex’ is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the ‘line’ variable can, of course, be assigned to any database field or control on the form as desired.

The Results List Item WHEN-LIST-CHANGED trigger retrieves and displays the full address details for the selected address.

The ‘Results’ List Item WHEN-LIST-CHANGED Trigger

This trigger contains the following code:

				
					DECLARE
    postcode varchar(255);
    listIndex integer;
    countyType integer;
    retVal integer;
    locRec integer;
    locRecs integer;
    addrRec integer;
    dpts integer;
    changeFlag integer;
    organisation varchar(255);
    property varchar(255);
    street varchar(255);
    locality varchar(255);
    town varchar(255);
    county varchar(255);
    pcode varchar(255);
BEGIN
    countyType := 3;
    listIndex := :block4.results;
    IF listIndex > 0 THEN
        postcode := GET_LIST_ELEMENT_LABEL('results', listindex); 
        locRec := GET_LIST_ELEMENT_LABEL('resultIndex', listIndex) / 1000; 
        addrRec := GET_LIST_ELEMENT_LABEL('resultIndex', listIndex) - (locRec * 1000); 
        locRecs := GetPostcode(postcode, locRec, dpts, changeFlag);
        retVal := GetAddress(addrRec);
        GetFormattedAddress(organisation, property, street, locality, town, county, pcode, countyType);
        :TxtOrganisation := organisation;
        :TxtProperty := property;
        :TxtStreet := street;
        :TxtLocality := locality;
        :TxtTown := town;
        :TxtCounty := county;
        :TxtPostcode := pcode;
    END IF;
END;

This trigger method look’s up the address which the user has selected and fills it’s details into the Text Item’s for these fields on the sample Oracle Form.

It retrieves the postcode from the results List Item for the selected record and the locality and address indexes of the selected record from the hidden resultsindex List Item.

Postcode Plus Functions

GetPostcode

Used to carry out a Postcode lookup and retrieve the associated postcode locality information.

Declaration

				
					CREATE OR REPLACE FUNCTION GetPostcode(
pcode varchar2, recNo BINARY_INTEGER, dpts OUT BINARY_INTEGER, changeFlag OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetPostcode" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
pcode string, recNo long, dpts long, changeFlag long);

Usage

				
					postcode varchar(255);
locRecs integer; 
locRec integer; 
dpts integer; 
changeFlag integer; 
BEGIN 
postcode := 'B11 1AA'; 
locRec := 1; 
locRecs := GetPostcode(postcode, locRec, dpts, changeFlag); 
IF locRecs < 1 THEN 
  -- Postcode not found - Display error? 
ELSE 
  LOOP 
    -- Call of Address records - see GetAddress Function 
    locRec := locRec + 1; 
    EXIT WHEN locRec > locRecs; 
    -- Some postcodes have multiple localities - get these too 
    locRecs : = GetPostcode(postcode, locRec, dpts, changeFlag); 
  END LOOP; 
END IF; 
END;

Result

locRecs indicates number of postcode localities found for this lookup.

-1 if postcode is not of the correct format
-2 if postcode does not exist
-3 if record number for call-off is out of range

dpts indicates the number of delivery points on this postcode – useful for subsequent calls to GetAddress to get the address records.

changeFlag is set to 1 to indicate the Postcode has been changed following a Royal Mail recoding. The records for the new postcode are returned and the postcode parameter now also contains the new postcode.

GetAddress

Used to fetch the delivery point (address) data following a call to GetPostcode or GetMatch.

Declaration

				
					CREATE OR REPLACE FUNCTION GetAddress(recNo BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetAddress" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);

Usage

				
					retVal integer; 
addrRec integer; 
BEGIN 
-- This assumes GetPostcode or GetMatch have already been called and dpts holds the number of delivery points
addrRec := 1; 
LOOP 
  EXIT WHEN addrRec > dpts; 
  retVal := GetAddress(addrRec); 
  IF retVal = 1 THEN -- Matching record (0 will be returned for records not matching
    -- search or fast find criteria) 
    -- Call GetAddressLine, GetFormattedAddress, and/or GetLocality and     GetAddress to get the address data to display or place in your database table
  END IF; 
  addrRec := addrRec + 1; 
END LOOP; 
END;

Result

retVal indicates the success of the address call-off.

1 if call-off follows a call to GetMatch and address record matches search criteria
0 if call-off follows a call to GetMatch and address record does NOT match search criteria
-3 if record number for call-off is out of range

GetAddressLine

Used to get a line containing address details following a call to GetAddress. This line is formatted for list display and to provide for user selection. GetFormattedAddress returns fields separately for assignment to fields in your database table. GetLocality and GetAddressRec provide raw address records if you need to format the address yourself to suit your database format.

Declaration

				
					CREATE OR REPLACE PROCEDURE GetAddressLine(
line OUT varchar2, countyType BINARY_INTEGER)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetAddressLine"
LANGUAGE C
PARAMETERS (line string, countyType long);

Usage

				
					line varchar(512); 
countyType integer; 
BEGIN 
-- This assumes GetAddress has already been called following a lookup, 
-- fast find or search. 
countyType := 3; 
GetAddressLine(line, countyType); 
-- line now contains the address line 
END;

Parameters

countyType

The type of counties you wish to display. The options for these are as follows:

0 – Omit County
1 – Postal Counties
2 – Abbreviated Postal Counties
3 – Optional and Postal Counties
4 – Abbreviated Optional and Postal Counties
5 – Traditional Counties
6 – Administrative Counties

Result

line contains the address line returned as a string for display. Normally you would provide the user with a list of these called from each iteration of a call to GetAddress following a search, lookup or fast find.

GetFormattedAddress

Used to return a full address formatted as would be used on an address label following a call to GetAddress. GetLocality and GetAddressRec provide raw address records if you need to format the address yourself to suit your database format or require additional (non address) data from Postcode Plus.

Declaration

				
					CREATE OR REPLACE PROCEDURE GetFormattedAddress(
organisation OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2,
town OUT varchar2, county OUT varchar2, pcode OUT varchar2, countyType BINARY_INTEGER)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetFormattedAddress"
LANGUAGE C
PARAMETERS (organisation string, property string, street string, locality string, town string,
county string, pcode string, countyType long);

Usage

				
					organisation varchar(255); 
property varchar(255); 
street varchar(255); 
locality varchar(255); 
town varchar(255); 
county varchar(255); 
postcode varchar(255); 
countyType integer; 
BEGIN 
-- This assumes GetAddress has already been called following a lookup,
-- fast find or search. 
countyType := 3; 
GetFormattedAddress(organisation, property, street, locality, town, county, postcode, countyType); 
-- parameters supplied to GetFormattedAddress now contain the address
-- fields so can be displayed or assigned to your database table. 
END;

Parameters

countyType

The type of counties you wish to display. The options for these are as follows:

0 – Omit County
1 – Postal Counties
2 – Abbreviated Postal Counties
3 – Optional and Postal Counties
4 – Abbreviated Optional and Postal Counties
5 – Traditional Counties
6 – Administrative Counties

GetLocality

Used to return raw locality record if you need to format the address yourself to suit your database format or require additional (non address) data from Postcode Plus. This should be called after a call to GetMatch or GetPostcode.

Declaration

				
					CREATE OR REPLACE PROCEDURE GetLocality(
pcode OUT varchar2, depStreet OUT varchar2, street OUT varchar2, dblDepLoc OUT varchar2, depLoc OUT varchar2, town OUT varchar2, countySource OUT varchar2, county OUT varchar2, countyAbbr OUT varchar2, mailsort OUT varchar2, stdCode OUT varchar2, gridE OUT varchar2, gridN OUT varchar2, wardCode OUT varchar2, wardName OUT varchar2, nhsCode OUT varchar2, nhsName OUT varchar2, nhsRegionCode OUT varchar2, nhsRegion OUT varchar2, deliveryPoints OUT varchar2, userType OUT varchar2, constituency OUT varchar2, tvRegion OUT varchar2, authority OUT varchar2, tradCounty OUT varchar2, adminCounty OUT varchar2, censusCode OUT varchar2, affluence OUT varchar2, lifeStage OUT varchar2, additionalCensusInfo OUT varchar2, occupancy OUT varchar2, addressType OUT varchar2, pctCode OUT varchar2, pctName OUT varchar2, eerCode OUT varchar2, eerName OUT varchar2, urbanRuralCode OUT varchar2, urbanRuralName OUT varchar2, leaCode OUT varchar2, leaName OUT varchar2, latitude OUT varchar2, longitude OUT varchar2)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetLocRec4"
LANGUAGE C
PARAMETERS (pcode string, depStreet string, street string, dblDepLoc string, depLoc string,
town string, countySource string, county string, countyAbbr string, mailsort string,
stdCode string, gridE string, gridN string, wardCode string, wardName string,
nhsCode string, nhsName string, nhsRegionCode string, nhsRegion string,
deliveryPoints string, userType string, constituency string, tvRegion string,
authority string, tradCounty string, adminCounty string, censusCode string, affluence string, lifeStage string, additionalCensusInfo string, occupancy string, addressType string, pctCode string, pctName string, eerCode string, eerName string, urbanRuralCode string, urbanRuralName string, leaCode string, leaName string, latitude String, longitude string);
/

Usage

				
					postcode varchar(255); 
depStreet varchar(255); 
street varchar(255); 
dblDepLoc varchar(255); 
depLoc varchar(255); 
town varchar(255); 
countySource varchar(255); 
county varchar(255); 
countyAbbr varchar(255); 
mailsort varchar(255); 
stdCode varchar(255); 
gridE varchar(255); 
gridN varchar(255); 
wardCode varchar(255); 
wardName varchar(255); 
nhsCode varchar(255); 
nhsName varchar(255); 
nhsRegionCode varchar(255); 
nhsRegion varchar(255); 
deliveryPoints varchar(255); 
userType varchar(255); 
constituency varchar(255); 
tvRegion varchar(255); 
authority varchar(255); 
tradCounty varchar(255); 
adminCounty varchar(255); 
censusCode varchar(255); 
affluence varchar(255); 
lifeStage varchar(255); 
additionalCensusInfo varchar(255); 
occupancy varchar(255); 
addressType varchar(255); 
pctCode varchar(255); 
pctName varchar(255); 
eerCode varchar(255); 
eerName varchar(255); 
urbanRuralCode varchar(255); 
urbanRuralName varchar(255); 
leaCode varchar(255); 
leaName varchar(255); 
latitude varchar(255); 
longitude varchar(255); 
BEGIN 
-- This assumes GetPostcode or GetMatch has already been called to get
-- locality level data 
GetLocality(postcode, depStreet, street, dblDepLoc, depLoc, town, countySource, county, countyAbbr, mailsort, stdCode, gridE, gridN, wardCode, wardName, nhsCode, nhsName, nhsRegionCode, nhsRegion, deliveryPoints, userType, constituency, tvRegion, authority, tradCounty, adminCounty, censusCode, affluence, lifeStage, additionalCensusInfo, occupancy, addressType, pctCode, pctName, eerCode, eerName, urbanRuralCode, urbanRuralName, leaCode, leaName, latitude, longitude); 
-- parameters supplied to GetLocality now contain the locality level
-- Postcode Plus fields so can be displayed or assigned to your database table

See the Postcode Plus manual for information relating to each parameter returned by this function (they match those in the locality record detailed in the Postocde Plus manual API section).

GetAddressRec

Used to return raw address record if you need to format the address yourself to suit your database format or require additional (non address) data from Postcode Plus. This should be called after a call to GetAddress.

Declaration

				
					CREATE OR REPLACE PROCEDURE GetAddressRec(
poBox OUT varchar2, organisation OUT varchar2, department out varchar2, number OUT varchar2,
subBuilding OUT varchar2, building OUT varchar2, dps OUT varchar2, households OUT varchar2, udprn OUT varchar2, justBuilt OUT varchar2)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetAddrRec2"
LANGUAGE C
PARAMETERS (poBox string, organisation string, department string, number string, subBuilding string,
building string, dps string, households string, udprn string, justBuilt string);
/

Usage

				
					poBox varchar(255); 
organisation varchar(255); 
department varchar(255); 
number varchar(255); 
subBuilding varchar(255); 
building varchar(255); 
dps varchar(255); 
households varchar(255); 
udprn varchar(255); 
justBuilt varchar(255); 
BEGIN 
-- This assumes GetAddress has already been called to get address level data 
GetAddressRec(poBox, organisation, department, number, subBuilding, building, dps, households, udprn, justBuilt); 
-- parameters supplied to GetAddressRec now contain the locality level
-- Postcode Plus fields so can be displayed or assigned to your database table. 
END;

See the Postcode Plus manual for information relating to each parameter returned by this function (they match those in the address record detailed in the Postocde Plus manual API section).

FastFind

Used to find matching addresses based on a comma separated search string. Identical to that used in the Find Box of the Postcode Plus front-end. For example a postcode can be entered here, e.g. ‘B11 1AA’ just like that used in a call to GetPostcode, searches such as ‘Commercial Street, Birmingham’ can be entered, or an address can be looked up with property information to save a user having to select it from a list e.g., ‘274, B11 1AA’.

Declaration

				
					CREATE OR REPLACE FUNCTION FastFind(findStr varchar2, changeFlag OUT BINARY_INTEGER, oldPcode OUT varchar2, newPcode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoFastFind" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (findStr string, changeFlag long, oldPcode string, newPcode string);

Usage

				
					fastStr string; 
changeflag integer; 
pcodeIn varchar(255); 
pcodeOut varchar(255); 
retVal integer; 
record integer; 
dpts integer; 
BEGIN 
fastStr := 'Commercial Street,Birmingham'; 
retVal: = FastFind(fastStr, changeFlag, pcodeIn, pcodeOut); 
-- Call GetMatch to retrieve matching records 
END;

Parameters

fastStr

Used to specify the free text string to search for. Any excess characters after the first 256 will be ignored.

changeFlag, pcodeIn, pcodeOut

changeFlag is set to 1 if a postcode has been included in the fast find string which has changed following a Royal Mail recoding. Results matching the new postcode will be returned. If the postcode has been changed then pcodeIn returns the postcode that the user specified and pcodeOut returns the postcode it has changed to. These parameters are useful if you wish to report the postcode change to the user.

Result

retVal indicates the return value of the FastFind function.

> 0 – the number of sectors with possible matching records if success
-2 if no matches were found.

InitiateSearch

Used to start an Address Search to find addresses matching your criteria.

Declaration

				
					CREATE OR REPLACE FUNCTION InitiateSearch(pcFrom varchar2, pcTo varchar2, stdCode varchar2, org varchar2, building varchar2, depStreet varchar2, street varchar2, dblDepLoc varchar2, depLoc varchar2, town varchar2, county varchar2, justBuilt varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoSearch2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (pcFrom string, pcTo string, stdCode string, org string, building string, depStreet string, street string, dblDepLoc string, depLoc string, town string, county string, justBuilt string);
/

Usage

				
					pcodeFrom varchar(255); 
pcodeTo varchar(255); 
stdCode varchar(255); 
organisation varchar(255); 
building varchar(255); 
depStreet varchar(255); 
street varchar(255); 
dblDepLoc varchar(255); 
depLoc varchar(255); 
town varchar(255); 
county varchar(255); 
justBuilt varchar(255); 
retVal integer; 
BEGIN 
pcodeFrom:= ''; 
pcodeTo := ''; 
stdCode := ''; 
organisation := ''; 
building := ''; 
depStreet := ''; 
street := streetIn; 
dblDepLoc := ''; 
depLoc := ''; 
town := townIn; 
county := ''; 
justBuilt := ''; 
retVal := InitiateSearch(pcodeFrom, pcodeTo, stdCode, organisation, building, depStreet, street, dblDepLoc, depLoc, town, county, justBuilt); 
-- Call GetMatch to retrieve matching records 
END;

The parameters to the InitiateSearch function specify the criteria you wish to search on. Leave those that are not required blank.

Result

retVal indicates the return value from the InitiateSearch function.

> 0 – the number of sectors with possible matching records if success
-1 if pcodeFrom or pcodeTo is not a valid format
-2 if pcodeFrom and pcodeTo are specified and there are no postcodes within the specified range
-2 if no matches were found

GetMatch

Used to return the associated postcode locality information following a Search or Fast Find.

Declaration

				
					CREATE OR REPLACE FUNCTION GetMatch (dpts OUT BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoGetMatch" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (dpts long);

Usage

				
					locRec integer; 
addrRec integer; 
dpts integer; 
BEGIN 
LOOP 
  locRec := GetMatch(dpts); 
  EXIT WHEN locRec < 0; 
  addrRec := 1; 
  IF locRec > 0 THEN 
    -- Call GetAddress to retrieve each matching address record in this locality 
  END IF; 
END LOOP; 
END;

Result

ReturnType indicates the success of the address call-off:

1 if a matching Postcode Locality has been found – use GetAddress to call off address records
0 as it finishes each sector searched. This gives the opportunity to cancel long searches and also to show progress against the number of sectors which was returned by the InitiateSearch or FastFind function
-6 if search is completed

LookupUdprn

Used to lookup an address record directly from it’s UDPRN code.

Declaration

				
					CREATE OR REPLACE FUNCTION LookupUdprn(udprn varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY pcplus
NAME "DoLookupUdprn" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (udprn string);
/

Usage

				
					udprn varchar(255); 
retVal integer; 
BEGIN 
udprn := '00379666'; 
retVal := LookupUdprn(udprn); 
If retVal < 0 THEN 
  -- UDPRN not found - Display error? 
ELSE 
  -- Call GetAddressLine, GetFormattedAddress, and/or GetLocality and GetAddress to get the address data to display or place in your database
END IF; 
END;

result

ReturnType indicates the success of the UDPRN lookup:

>0 if a matching record has been found
<0 if udprn was not found

Configure Oracle for BankFinder

Install BankFinder on your Oracle Server then copy the bforacle.so (Unix) or bforacle.dll (Windows) to an appropriate location on the same server. Although the library can be placed anywhere, a suitable location would be the lib subfolder of your Oracle home path.

Ensure your listener.ora and tnsnames.ora in the network\admin subfolder of your Oracle home path are correctly configured to use this external process.

Your listener.ora file should be similar to the following:

				
					# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\listener.ora
# Generated by Oracle configuration tools.
LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
            )
            (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = servername)(PORT = 1521))
            )
        )
    )
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\ora92)
            (PROGRAM = extproc)
            (ENVS="EXTPROC_DLLS=ANY") 
        )
        (SID_DESC =
            (GLOBAL_DBNAME = name.domain)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = name)
        )
        (SID_DESC =
            (GLOBAL_DBNAME = Test)
            (ORACLE_HOME = C:\oracle\ora92)
            (SID_NAME = Test)
        )

				
					(ENVS="EXTPROC_DLLS=ANY")

To allow the DLL to run if it is not in your Oracle bin or lib folder. Using “ANY” is useful for testing, but for additional security you may prefer to use a line like:

				
					(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\bforacle.dll")

Please note that you can colon separate entries in that line if you already have another DLL listed, e.g.

				
					(ENVS="EXTPROC_DLLS=c:\oracle\ora92\lib\bforacle.dll:c:\inhouse\myinhouse.dll")

Your tnsnames.ora file along with details of your database names also needs to include details for the extproc listener as well, similar to the following:

				
					EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
        )
            (CONNECT_DATA =
                (SID = PLSExtProc)
                (PRESENTATION = RO)
            )
        )

Again this should have been configured for you when you installed Oracle.

If you need further assistance configuring Oracle to use our DLL then please email Support. If you query relates to more complex issues regarding Oracle database configuration generally then please contact your normal database support provider.

Add the Functions

To declare all the functions in the bfOracle.dll to enable you to use all the functionality of AFD BankFinder through SQL statements or stored procedures you will need to add the following Functions and Procedures to your database. The easiest way to do this is to launch SQL Plus on your Oracle server and enter the following statements. You can enter and/or manage these how you wish of course. Please note that due to limitations under some versions of UNIX some of the longer function calls may need to be entered on fewer lines (i.e. with longer lines).

Windows:

create library afdbank as ‘c:\oracle\ora92\lib\bforacle.dll’;

Unix:

create library afdbank as ‘/opt/Oracle/OraHome/lib/bforacle.so’;

				
					CREATE OR REPLACE FUNCTION BFGetFirst(
flags IN OUT BINARY_INTEGER, lookupStr varchar2, sortCode varchar2, bankBIC varchar2, branchBIC varchar2, postcode varchar2, branchName varchar2, bankName varchar2,
town varchar2, phone varchar2, searchText varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirst2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
flags long, lookupStr string, sortCode string, bankBIC string, branchBIC string, postcode string, branchName string, bankName string, town string, phone string, searchText string);
/
CREATE OR REPLACE FUNCTION BFGetNext
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNext" -- Name of function call. Quotes preserve lower case.
LANGUAGE C;
/
CREATE OR REPLACE FUNCTION BFGetFirstRedirect(sortCode IN OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirstRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
/
CREATE OR REPLACE FUNCTION BFGetNextRedirect(sortCode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNextRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
/
CREATE OR REPLACE FUNCTION BFJumpToRecord (recNo BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoJumpToRecord" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);
/
CREATE OR REPLACE FUNCTION BFValidateAccount(flags IN OUT BINARY_INTEGER, sortCode IN OUT varchar2, accountNo IN OUT varchar2, typeOfAccountCode OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateAccount2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (flags long, sortCode string, accountNo string, typeOfAccountCode long);
/
CREATE OR REPLACE FUNCTION BFValidateCard(revisionID BINARY_INTEGER, cardNo IN varchar2, expiryDate IN varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateCard" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (revisionID long, cardNo string, expiryDate string);
/
CREATE OR REPLACE PROCEDURE BFGetBankRecord(
recNo OUT BINARY_INTEGER, sortCode OUT varchar2, bankBIC OUT varchar2, branchBIC OUT varchar2, subBranchSuffix OUT varchar2, shortBranchTitle OUT varchar2, centralBankCountryCode OUT varchar2, supervisoryBody OUT varchar2, deletedDate OUT varchar2, branchTypeIndicator OUT varchar2, mainBranchSortCode OUT varchar2, majorLocationName OUT varchar2, minorLocationName OUT varchar2, branchName OUT varchar2, branchName2 OUT varchar2, fullBranchTitle OUT varchar2, ownerBankShortName OUT varchar2, ownerBankFullName OUT varchar2, ownerBankCode OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2, town OUT varchar2, county OUT varchar2, postcode OUT varchar2, stdCode OUT varchar2, phone OUT varchar2, faxStdCode OUT varchar2, fax OUT varchar2, countryIndicator OUT varchar2, reserved1 OUT varchar2, reserved2 OUT varchar2, reserved3 OUT varchar2, reserved4 OUT varchar2, reserved5 OUT varchar2) AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankRecord2"
LANGUAGE C
PARAMETERS (recNo long, sortCode string, bankBIC string, branchBIC string, 
subBranchSuffix string, shortBranchTitle string, centralBankCountryCode string,
supervisoryBody string, deletedDate string, branchTypeIndicator string,
mainBranchSortCode string, majorLocationName string, minorLocationName string,
branchName string, branchName2 string, fullBranchTitle string,
ownerBankShortName string, ownerBankFullName string, ownerBankCode string,
property string, street string, locality string, town string,
county string, postcode string, stdCode string, phone string, faxStdCode string, fax string, countryIndicator string, reserved1 string, reserved2 string, reserved3 string, reserved4 string, reserved5 string);
/
CREATE OR REPLACE PROCEDURE BFGetBACSRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
redirectedFromFlag OUT varchar2, redirectedToSortCode OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, settlementSection OUT varchar2,
settlementSubSection OUT varchar2, handlingBankCode OUT varchar2,
handlingBankShortName OUT varchar2, handlingBankFullName OUT varchar2,
handlingBankStream OUT varchar2, accountNumberedFlag OUT varchar2,
DDIVoucherFlag OUT varchar2, allowedDirectDebits OUT varchar2,
allowedBankGiroCredits OUT varchar2, allowedBuildingSocietyCredits OUT varchar2,
allowedDividendPayments OUT varchar2, allowedDirectDebitInstructions OUT varchar2,
allowedUnpaidChequeClaims OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBACSRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
redirectedFromFlag string, redirectedToSortCode string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, settlementSection string,
settlementSubSection string, handlingBankCode string,
handlingBankShortName string, handlingBankFullName string,
handlingBankStream string, accountNumberedFlag string,
DDIVoucherFlag string, allowedDirectDebits string,
allowedBankGiroCredits string, allowedBuildingSocietyCredits string,
allowedDividendPayments string, allowedDirectDebitInstructions string,
allowedUnpaidChequeClaims string);
/
CREATE OR REPLACE PROCEDURE BFGetCHAPSRecord(
pStatus OUT varchar2, pLastChange OUT varchar2, pClosedClearing OUT varchar2, 
pSettlementBankCode OUT varchar2, pSettlementBankShortName OUT varchar2,
pSettlementBankFullName OUT varchar2,
eStatus OUT varchar2, eLastChange OUT varchar2, eClosedClearing OUT varchar2, 
eEuroRoutingBICBank OUT varchar2, eEuroRoutingBICBranch OUT varchar2,
eSettlementBankCode OUT varchar2, eSettlementBankShortName OUT varchar2,
eSettlementBankFullName OUT varchar2, eReturnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCHAPSRecord"
LANGUAGE C
PARAMETERS (pStatus string, pLastChange string, pClosedClearing string, 
pSettlementBankCode string, pSettlementBankShortName string,
pSettlementBankFullName string,
eStatus string, eLastChange string, eClosedClearing string, 
eEuroRoutingBICBank string, eEuroRoutingBICBranch string,
eSettlementBankCode string, eSettlementBankShortName string,
eSettlementBankFullName string, eReturnIndicator string);
/
CREATE OR REPLACE PROCEDURE BFGetCCCCRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, debitAgencySortCode OUT varchar2,
returnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCCCCRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, debitAgencySortCode string,
returnIndicator string);
/
CREATE OR REPLACE PROCEDURE BFGetBankLine(line OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankLine"
LANGUAGE C
PARAMETERS (line string);
/

Full details of each of these Function calls is given in the Function reference.

Using the API

Run the BFLookup function to check everything is working. You can then add the BankFinder functionality to your Oracle system using PL/SQL.

BankFinder Examples

BFLookup

To test you have correctly installed and configured BankFinder for Oracle on your server you can paste the following test procedure into SQL Plus to perform a simple lookup.

				
					CREATE OR REPLACE PROCEDURE BFLookup (lookupStr varchar2) AS
  line varchar2(255);
  retVal integer;
  flags integer;
BEGIN
  -- Set flags to 0 to return branches on both the UK and Irish clearing systems
  -- Set flags to 1 to return branches on the UK (BACS) clearing system only
  -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
  flags := 0;
  retVal := BFGetFirst(flags, lookupStr, '', '', '', '', '', '', '', '', '');
  IF retVal < 0 THEN
    IF retVal < -2 THEN
      dbms_output.put_line(retVal);
    ELSE
      dbms_output.put_line('No Matching Records Found');
    END IF;
  ELSE
    LOOP
      IF retVal <>  5 THEN   -- 5 is a record break
        BFGetBankLine(line);
        dbms_output.put_line(line);
      END IF;
      retVal := BFGetNext();
      EXIT WHEN retVal < 0;
    END LOOP;
  END IF;
END;
/

Before executing this procedure, set serveroutput to on so that you will see it’s output, by entering the following line into SQL Plus:

				
					set serveroutput on;

Now to test a lookup execute the function supplying it with a valid lookup string, for example:

				
					execute BFLookup('517032');

You will then see a list of the bank branches using the sortcode 517032.

See the function reference and the details of our Oracle Forms example for details of how the functions called in this procedure work and how to use them in your own database application.

Oracle Forms

You can use BankFinder for Oracle in Oracle Forms easily once you have installed and configured the library to run on your Oracle server. Code can be written to call the external procedures and the results can be assigned to on-screen controls for display or to table fields etc. simply by writing SQL in your Oracle Forms trigger methods.

You may find looking at the code contained in that form and the descriptions of the methods here invaluable in assisting in your integration of Oracle with BankFinder in other applications as well.

You can save the Oracle Form source code to try out in Oracle Forms Developer yourself from here. It demonstrates the use of a lookup, search, and account and card validation using BankFinder for Oracle. Simply load the afdbank.fmb file into Oracle Forms and connect to the database as a user which is configured to access BankFinder for Oracle Procedures and Functions. You can then run the form as well as examine it’s code to use as a basis for your own integration.

The ‘Fast Find’ Button WHEN_MOUSE_CLICK Trigger

This trigger contains the following code:

				
					DECLARE
    fastFindStr varchar(255);
    line varchar2(512);
    retVal integer;
    listElement Number;
    indexValue integer;
    recNo integer;
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    town varchar(255); 
    county varchar(255); 
    postcode varchar(255); 
    stdCode varchar(255); 
    phone varchar(255); 
    faxStdCode varchar(255); 
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
    flags integer;
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    fastFindStr := :block4.find;
    retVal := BFGetFirst(flags, fastFindStr, ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ');
    CLEAR_LIST('results');
    listElement := 0;
    IF retVal < 0 THEN
        MESSAGE('No Matching Records Found', ACKNOWLEDGE);
    ELSE
        LOOP
            IF retVal <> 5 THEN -- 5 is a record break
                BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5);
                BFGetBankLine(line);
                listElement := listElement + 1;
                indexValue := recNo;
                Add_List_Element('results', listElement, line, listElement);
                Add_List_Element('resultindex', listElement, indexValue, listElement);
            END IF; 
            retVal := BFGetNext();
            EXIT WHEN retVal < 0;
        END LOOP;
    END IF;
END;

This trigger method queries BankFinder for the fast find string specified in the find Text Item. It retrieves all matching bank records and uses the ADD_LIST_ELEMENT built-in subprogram to add each result to the results List Item. A hidden List Item ‘resultsindex’ is used to store the indexes of each record so they can be retrieved when an item is selected in the list. Rather than adding items to the List Item the ‘line’ variable can, of course, be assigned to any database field or control on the form as desired.

The ‘Search’ Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

				
					DECLARE
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    postcode varchar(255); 
    branchName varchar(255); 
    bankName varchar(255);
    town varchar(255); 
    phone varchar(255); 
    searchText varchar(255); 
    retVal integer;
    listElement Number;
    indexValue integer;
    searchLimit integer;
    line varchar2(512);
    recNo integer;
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    county varchar(255); 
    stdCode varchar(255); 
    faxStdCode varchar(255);
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
    flags integer;
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    searchLimit := 1000; -- Set to stop Oracle going off retrieving infinite results with a vague search
    sortCode:= :txtSortCode;
    if sortCode IS NULL THEN sortCode := ' '; END IF;
    postcode := :txtPostcode;
    if postcode IS NULL THEN postcode := ' '; END IF;
    branchName := :txtBranchName;
    if branchName IS NULL THEN branchName := ' '; END IF;
    bankName := :txtBankName;
    if bankName IS NULL THEN bankName := ' '; END IF;
    town := :txtTown;
    if town IS NULL THEN town := ' '; END IF;
    phone := :txtPhone;
    if phone IS NULL THEN phone := ' '; END IF;
    bankBIC := :txtBankBIC;
    if bankBIC IS NULL THEN bankBIC := ' '; END IF;
    branchBIC := :txtBranchBIC;
    if branchBIC IS NULL THEN branchBIC := ' '; END IF;
    searchText := ' ';
    -- Do the search 
    CLEAR_LIST('results');
    retVal := BFGetFirst(flags, ' ', sortCode, bankBIC, branchBIC, postcode, branchName, bankName, town, phone, searchText);
    listElement := 0;
    IF retVal < 0 THEN
        MESSAGE('No Matching Records Found', ACKNOWLEDGE);
    ELSE
        LOOP
            IF retVal <> 5 THEN -- 5 is a record break
                BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdcode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5);
                BFGetBankLine(line);
                listElement := listElement + 1;
                indexValue := recNo;
                Add_List_Element('results', listElement, line, listElement);
                Add_List_Element('resultindex', listElement, indexValue, listElement);
            END IF; 
            EXIT WHEN listElement > searchLimit;
            retVal := BFGetNext();
            EXIT WHEN retVal < 0;
        END LOOP;
    END IF;
END;

This trigger method queries BankFinder for the search criteria specified in the text fields.

The Results List Item WHEN-LIST-CHANGED trigger retrieves and displays the full address details for the selected address.

The ‘Validate Account’ Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

				
					DECLARE
    sortCode varchar(255);
    accountNo varchar2(255);
    retVal integer;
    typeOfAccountCode integer;
    flags integer;
    clearingSystem varchar2(255);
BEGIN
    -- Set flags to 0 to return branches on both the UK and Irish clearing systems
    -- Set flags to 1 to return branches on the UK (BACS) clearing system only
    -- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
    flags := 0;
    sortcode := :txtSort;
    if sortCode IS NULL THEN sortCode := ' '; END IF;
    accountNo := :txtAccount;
    if accountNo IS NULL THEN accountNo := ' '; END IF;
    retVal := BFValidateAccount(flags, sortCode, accountNo, typeOfAccountCode);
    IF retVal >= 0 THEN
        -- Update fields in-case of translation
        :txtSort := sortCode;
        :txtAccount := accountNo;
        CASE flags
          WHEN 1 THEN clearingSystem := 'UK (BACS)';
          WHEN 2 THEN clearingSystem := 'IRISH (IPSO)';
          WHEN 3 THEN clearingSystem := 'UK (BACS) & IRISH(IPSO)';
        END CASE;
        -- Inform user of success
        IF retVal = 0 THEN
            MESSAGE('Account Number Valid - Branch a member of the clearing System ' || clearingSystem || '. (Type Of Account Code:' || TO_CHAR(typeOfAccountCode) || ')', ACKNOWLEDGE);
        ELSE
            MESSAGE('Validation Not Avaliable - Branch a member of the clearing System ' || clearingSystem || '. (Type Of Account Code:' || TO_CHAR(typeOfAccountCode) || ')', ACKNOWLEDGE);
        END IF;
    ELSE
        CASE retVal
            WHEN -2 THEN MESSAGE('Sort Code Does Not Exist', ACKNOWLEDGE);
            WHEN -4 THEN MESSAGE('Error opening BankFinder data files', ACKNOWLEDGE);
            WHEN -7 THEN MESSAGE('Data Licence Invalid or Expired', ACKNOWLEDGE);
            WHEN -13 THEN MESSAGE('Invalid Sort Code', ACKNOWLEDGE);
            WHEN -14 THEN MESSAGE('Invalid Account Number', ACKNOWLEDGE);
        END CASE;
    END IF;
END;

This trigger method queries BankFinder to find out if the sort code and account number entered are valid. You should note that if successfully validated the sortcode and account number may be updated should any account number translation be necessary (e.g. with 10 digit account numbers). You should therefore use the sortcode and account number returned by the BFValidateAccount function for any subsequent processing and not the originally entered numbers. The typeOfAccountCode return value is necessary for BACS processing with some translated account numbers.

The ‘Validate Card’ Button WHEN-BUTTON-PRESSED Trigger

This trigger contains the following code:

				
					DECLARE
    cardNo varchar(255);
    expiryDate varchar2(255);
    retVal integer;
    revisionID integer;
BEGIN
    revisionID := 2;
    cardNo := :txtCard;
    if cardNo IS NULL THEN cardNo := ' '; END IF;
    expiryDate := :txtExpiry;
    if expiryDate IS NULL THEN expiryDate := ' '; END IF;
    retVal := BFValidateCard(revisionID, cardNo, expiryDate);
    CASE retVal
        WHEN 1 THEN MESSAGE('Valid Mastercard', ACKNOWLEDGE);
        WHEN 2 THEN MESSAGE('Valid Visa', ACKNOWLEDGE);
        WHEN 3 THEN MESSAGE('Valid American Express', ACKNOWLEDGE);
        WHEN 4 THEN MESSAGE('Valid Switch', ACKNOWLEDGE);
        WHEN 5 THEN MESSAGE('Valid Visa Delta', ACKNOWLEDGE);
        WHEN 6 THEN MESSAGE('Valid Visa UK Electron', ACKNOWLEDGE);
        WHEN 7 THEN MESSAGE('Valid Visa Purchasing', ACKNOWLEDGE);
        WHEN 8 THEN MESSAGE('Valid Switch and Maestro', ACKNOWLEDGE);
        WHEN 9 THEN MESSAGE('Valid Maestro', ACKNOWLEDGE);
        WHEN 10 THEN MESSAGE('Valid Solo and Maestro', ACKNOWLEDGE);
        WHEN 11 THEN MESSAGE('Valid JCB', ACKNOWLEDGE);
        WHEN -4 THEN MESSAGE('Error opening BankFinder data files', ACKNOWLEDGE);
        WHEN -7 THEN MESSAGE('Data Licence Invalid or Expired', ACKNOWLEDGE);
        WHEN -15 THEN MESSAGE('Invalid Expiry Date', ACKNOWLEDGE);
        WHEN -16 THEN MESSAGE('Card Expired', ACKNOWLEDGE);
        WHEN -18 THEN MESSAGE('Card Number Invalid', ACKNOWLEDGE);
        WHEN -19 THEN MESSAGE('Visa ATM Card Only', ACKNOWLEDGE);
        WHEN -20 THEN MESSAGE('Card Number Valid, but not of a recognised type', ACKNOWLEDGE);
    END CASE;
END;

This trigger method queries BankFinder to find out if the card number and expiry date entered are valid and lets you know what type of card the number corresponds with.

The ‘Results’ List Item WHEN-LIST-CHANGED Trigger

This trigger contains the following code:

				
					DECLARE
    listIndex integer;
    retVal integer;
    recNo integer;
    sortCode varchar(255); 
    bankBIC varchar(255); 
    branchBIC varchar(255); 
    subBranchSuffix varchar(255); 
    shortBranchTitle varchar(255); 
    centralBankCountryCode varchar(255); 
    supervisoryBody varchar(255); 
    deletedDate varchar(255); 
    branchTypeIndicator varchar(255); 
    mainBranchSortCode varchar(255); 
    majorLocationName varchar(255); 
    minorLocationName varchar(255); 
    branchName varchar(255); 
    branchName2 varchar(255); 
    fullBranchTitle varchar(255); 
    ownerBankShortName varchar(255); 
    ownerBankFullName varchar(255); 
    ownerBankCode varchar(255); 
    property varchar(255); 
    street varchar(255); 
    locality varchar(255); 
    town varchar(255); 
    county varchar(255); 
    postcode varchar(255); 
    stdCode varchar(255); 
    phone varchar(255); 
    faxStdCode varchar(255); 
    fax varchar(255); 
    countryIndicator varchar(255); 
    reserved1 varchar(255); 
    reserved2 varchar(255); 
    reserved3 varchar(255); 
    reserved4 varchar(255); 
    reserved5 varchar(255); 
BEGIN
    listIndex := :block4.results;
    IF listIndex > 0 THEN
        recNo := GET_LIST_ELEMENT_LABEL('resultIndex', listIndex); 
        retVal := BFJumpToRecord(recNo);
        IF retVal = 0 THEN
            BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5); 
            -- Use BFGetBACSRecord, BFGetCHAPSRecord and/or BFGetCCCCRecord to get other bank information you require
            :TxtSortCode := sortCode;
            :TxtPostcode := postcode;
            :TxtBranchName := branchName;
            :TxtBankName := ownerBankShortName;
            :TxtTown := town;
            :TxtPhone := stdCode || ' ' || phone;
            :TxtBankBIC := bankBIC;
            :TxtBranchBIC := branchBIC;
            :TxtSuffix := subBranchSuffix;
        END IF; 
    END IF;
END;

This trigger method look’s up the bank record which the user has selected and fills it’s details into the Text Item’s for these fields on the sample Oracle Form. You should note that if you require additional bank information you can also call the BFGetBACSRecord, BFGetCHAPSRecord and/or BFGetCCCCRecord procedures to retrieve those details. The list is filled when a lookup or search is carried out on the example form.

It retrieves the record number from the hidden resultsindex List Item which it uses in a call to BFJumpToRecord to return to the appropriate record in BankFinder corresponding to the one selected in the results list.

Bankfinder Functions

BFGetFirst and BFGetNext

Used to carry out a Bank lookup or search and retrieve the matching branches.

Declarations

				
					CREATE OR REPLACE FUNCTION BFGetFirst(
flags IN OUT BINARY_INTEGER, lookupStr varchar2, sortCode varchar2, bankBIC varchar2, branchBIC varchar2, postcode varchar2, branchName varchar2, bankName varchar2,
town varchar2, phone varchar2, searchText varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirst2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (
flags long, lookupStr string, sortCode string, bankBIC string, branchBIC string, postcode string, branchName string, bankName string, town string, phone string, searchText string);
/
CREATE OR REPLACE FUNCTION BFGetNext
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNext" -- Name of function call. Quotes preserve lower case.
LANGUAGE C;

Usage (1) – Lookup

				
					lookupStr varchar(255);
retVal integer; 
flags integer;
BEGIN
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
lookupStr := '517032'; 
retVal := BFGetFirst(flags, lookupStr, '', '', '', '', '', '', '', '', ''); 
IF retVal < 0 THEN 
   -- No matching records found - Display error? 
ELSE 
  LOOP 
    IF retVal <> 5 THEN -- 5 is a record break 
      -- Display/store details, e.g. use BFGetBankLine for a list item 
      -- or BFGetBankDetails, BFGetBACSDetails, 
      -- BFGetCHAPSDetails and/or BFGetCCCCDetails to retrieve full
      -- bank information. 
    END IF; 
    -- Get Next Item 
    retVal := BFGetNext(); 
    -- Exit when no more records left 
    EXIT WHEN retVal < 0; 
  END LOOP; 
END IF; 
END;

Usage (2) – Search

				
					sortCode varchar(255);
bankBIC varchar(255); 
branchBIC varchar(255); 
postcode varchar(255); 
branchName varchar(255); 
bankName varchar(255); 
town varchar(255); 
phone varchar(255); 
searchText varchar(255); 
retVal integer; 
flags integer;
BEGIN
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
-- Set the search criteria 
sortCode = ''; 
bankBIC = ''; 
branchBIC = ''; 
postcode = ''; 
branchName = ''; 
bankName = 'Natwest'; 
town = 'Birmingham'; 
phone = ''; 
searchText = ''; 
-- Do the search 
retVal := BFGetFirst(flags, '', sortCode, bankBIC, branchBIC, postcode,
branchName, bankName, town, phone, searchText); 
IF retVal < 0 THEN 
  -- No matching records found - Display error? 
ELSE 
  LOOP 
    IF retVal <> 5 THEN -- 5 is a record break 
      -- Display/store details, e.g. use BFGetBankLine for a list item 
      -- or BFGetBankDetails, BFGetBACSDetails, 
      -- BFGetCHAPSDetails and/or BFGetCCCCDetails to retrieve full
      -- bank information. 
    END IF; 
    -- Get Next Item 
    retVal := BFGetNext(); 
    -- Exit when no more records left 
    EXIT WHEN retVal < 0; 
  END LOOP; 
END IF; 
END;

Result

retVal indicates the return value of the BFGetFirst/BFGetNext function:

0 if success (result retrieved)
-2 if no matching records found
-4 if there is an error opening the BankFinder data files
-6 indicates the end of the search (returned by BFGetNext when no results are left to retrieve)
-7 if there is a data licence error or your data licence has expired
-10 if no lookup/search data was supplied

BFGetFirstRedirect and BFGetNextRedirect

These functions are used to get a list of banks that are redirected to a sort code. When you lookup a bank record if the ‘Redirected From’ field contains ‘R’ this means that other sort codes are redirected to this one. Call BFGetFirstRedirect with that records sort code and it will return the first sort code that has been redirected to it, then repeatedly call GetNextRedirect until it returns -6 to get the rest of the records.

Declarations

				
					CREATE OR REPLACE FUNCTION BFGetFirstRedirect(sortCode IN OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetFirstRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);
CREATE OR REPLACE FUNCTION BFGetNextRedirect(sortCode OUT varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoGetNextRedirect" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (sortCode string);

Usage

				
					sortCode varchar(255); 
retVal integer; 
BEGIN 
sortCode := '401118'
-- Call BFGetFirstRedirect to get the first record 
retVal := BFGetFirstRedirect(sortCode); 
LOOP 
  EXIT WHEN retVal < 0; 
  -- Display or store sortCode which will have been replaced with the redirected
  -- sortcode 
  -- Retrieve next record: 
  retVal := BFGetNextRedirect(sortCode);
END LOOP; 
END;

Result

retVal indicates the return value of the BFGetFirstRedirect/BFGetNextRedirect function:

0 if success
-2 if no matching records found
-4 if there is an error opening the BankFinder data files
-6 if no matching/further matching sort codes were found
-7 if there is a data licence error or your data licence has expired

BFJumpToRecord

This function is used to jump back to a record that has previously been accessed. It is useful if you are listing results and need to recall a record when the user selects it. Each Bank record has a record number simply pass that to this function, you will be moved to that position from where you can call off the data you then require using the BFGetBankRecord, BFGetBACSRecord, BFGetCHAPSRecord and BFGetCCCCRecord methods as desired.

Declaration

				
					CREATE OR REPLACE FUNCTION BFJumpToRecord (recNo BINARY_INTEGER)
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoJumpToRecord" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (recNo long);

Usage

				
					recNo integer; 
countyType integer; 
BEGIN 
recNo := -- Record Number you wish to jump back too 
retVal := BFJumpToRecord(recNo); 
-- If retVal = 0 then successful jump 
END;

Result

retVal indicates the return value of the BFJumpToRecord function:

0 if success
-2 if the record number supplied does not exist
-4 if there is an error opening the BankFinder data files
-7 if there is a data licence error or your data licence has expired

BFValidateAccount

This function is used to verify that an Account Number is valid and therefore can be used to help eliminate errors in capturing an account number. The sortcode and accountNo parameters will be updated with the translated numbers for BACS processing if applicable, so you should use the values returned by the function in any subsequent processing. The TypeOfAccountCode parameter is normally zero but for some account translations will be set and is necessary for BACS submission. You may also want to lookup the Sort Code using the BFGetFirst and BFGetNext methods to verify the bank details are correct.

Declaration

				
					CREATE OR REPLACE FUNCTION BFValidateAccount(flags IN OUT BINARY_INTEGER, sortCode IN OUT varchar2, accountNo IN OUT varchar2, typeOfAccountCode OUT BINARY_INTEGER) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateAccount2" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (flags long, sortCode string, accountNo string, typeOfAccountCode long);
/

Usage

				
					sortCode varchar(255); 
accountNo varchar(255); 
typeOfAccountCode integer; 
retVal integer; 
flags integer;
BEGIN 
-- Set flags to 0 to return branches on both the UK and Irish clearing systems
-- Set flags to 1 to return branches on the UK (BACS) clearing system only
-- Set flags to 2 to return branches on the Irish (IPSO) clearing system only
flags := 0;
-- Set the account details to validate 
sortCode := '401101'; 
accountNo := '61893939'; 
-- Call the account validation function 
retVal := BFValidateAccount(flags, sortCode, accountNo, typeOfAccountCode);
IF retVal >= 0 THEN 
  -- Successfully validated - process it here - sortCode, accountNo, and
  -- typeOfAccountCode variables will have been updated with any necessary
  -- account translation 
  -- Flags will be 1 if the account is at a branch on the UK (BACS) System
  -- Flags will be 2 if the account is at a branch on the Irish (IPSO) System
  -- Flags will be 3 if the account is held at a branch on both clearing systems
ELSE 
  -- Validation failed - inform user (See below for error codes) 
END IF 
END;

Result

retVal indicates the return value of the BFValidateAccount function:

0 if success and validation was successful for the account code
1 also success, but no validation is available for this sort code
-2 if no matching sort code was found
-4 if there is an error opening the BankFinder data files
-7 if there is a data licence error or your data licence has expired
-13 if the sort code supplied is invalid (it should be in the format XXXXX, XX-XX-XX, or XX XX XX, where X is a digit)
-14 if the account number was invalid (incorrect length or the checksum was not valid)

BFValidateCard

This function is used to verify that a Debit / Credit Card Number and expiry date is valid and to determine what the card type is. It can be used to help eliminate errors in capturing debit or credit card details.

Declaration

				
					CREATE OR REPLACE FUNCTION BFValidateCard(revisionID BINARY_INTEGER, cardNo IN varchar2, expiryDate IN varchar2) 
RETURN BINARY_INTEGER AS 
EXTERNAL LIBRARY afdbank
NAME "DoValidateCard" -- Name of function call. Quotes preserve lower case.
LANGUAGE C
PARAMETERS (revisionID long, cardNo string, expiryDate string);

Usage

				
					cardNo varchar(255); 
expiryDate varchar(255); 
revisionID integer; 
retVal integer; 
BEGIN 
-- Revision ID should be set to 2 
revisionID = 2; 
-- Set the card details to validate 
cardNo := '6439401101618939396'; 
expiryDate := '04/02'; -- Must be in the format MM/YY or
-- set to an empty string if you don't want to check the expiry date
-- Call the card validation function 
retVal := BFValidateCard(revisionID, cardNo, expiryDate); 
IF retVal >= 0 THEN 
  -- Successfully validated - check return value for card type and process it here 
ELSE 
  -- Validation failed - inform user (See below for error codes) 
END IF 
END;

Result

retVal indicates the return value of the BFValidateCard function:

Returns Card Type (>0) if success:

1 if Mastercard
2 if Visa (not including Debit types listed below with values 5, 6 and 7 which are also Visa cards)
3 if American Express
4 if Switch (Will normally also need to get an Issue Number from the user, note Return value 8 is also Switch)
5 if Visa Delta
6 if Visa UK Electron
7 if Visa Purchasing
8 if Switch and Maestro combined (Will normally also need to get an Issue Number from the user for Switch)
9 if Maestro (note Return value 8 is also Maestro)
10 if Solo and Maestro combined
11 if JCB

Returns <0 if fails:

-4 if there is an error opening the BankFinder data files
-7 if there is a data licence error or your data licence has expired
-15 if an invalid expiry date was specified (it should be in the format MM/YY, or set to blank not to verify)
-16 if the card has passed it’s expiry date
-18 if the card number was invalid (Incorrect length, invalid number for the type of card, or the checksum was not valid)
-19 if the card is Visa but is only accepted by ATM machines (i.e. not for electronic use)
-20 if the card number itself is valid but the card could not be fully validated as it is not of one of the recognised types.

BFGetBankRecord

Used to return general bank details. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord.

Declaration

				
					CREATE OR REPLACE PROCEDURE BFGetBankRecord(
recNo OUT BINARY_INTEGER, sortCode OUT varchar2, bankBIC OUT varchar2, branchBIC OUT varchar2, subBranchSuffix OUT varchar2, shortBranchTitle OUT varchar2, centralBankCountryCode OUT varchar2, supervisoryBody OUT varchar2, deletedDate OUT varchar2, branchTypeIndicator OUT varchar2, mainBranchSortCode OUT varchar2, majorLocationName OUT varchar2, minorLocationName OUT varchar2, branchName OUT varchar2, branchName2 OUT varchar2, fullBranchTitle OUT varchar2, ownerBankShortName OUT varchar2, ownerBankFullName OUT varchar2, ownerBankCode OUT varchar2, property OUT varchar2, street OUT varchar2, locality OUT varchar2, town OUT varchar2, county OUT varchar2, postcode OUT varchar2, stdCode OUT varchar2, phone OUT varchar2, faxStdCode OUT varchar2, fax OUT varchar2, countryIndicator OUT varchar2, reserved1 OUT varchar2, reserved2 OUT varchar2, reserved3 OUT varchar2, reserved4 OUT varchar2, reserved5 OUT varchar2) AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankRecord2"
LANGUAGE C
PARAMETERS (recNo long, sortCode string, bankBIC string, branchBIC string, 
subBranchSuffix string, shortBranchTitle string, centralBankCountryCode string,
supervisoryBody string, deletedDate string, branchTypeIndicator string,
mainBranchSortCode string, majorLocationName string, minorLocationName string,
branchName string, branchName2 string, fullBranchTitle string,
ownerBankShortName string, ownerBankFullName string, ownerBankCode string,
property string, street string, locality string, town string,
county string, postcode string, stdCode string, phone string, faxStdCode string, fax string, countryIndicator string, reserved1 string, reserved2 string, reserved3 string, reserved4 string, reserved5 string);
/

Usage

				
					recNo integer; 
sortCode varchar(255); 
bankBIC varchar(255); 
branchBIC varchar(255); 
subBranchSuffix varchar(255); 
shortBranchTitle varchar(255); 
centralBankCountryCode varchar(255); 
supervisoryBody varchar(255); 
deletedDate varchar(255); 
branchTypeIndicator varchar(255); 
mainBranchSortCode varchar(255); 
majorLocationName varchar(255); 
minorLocationName varchar(255); 
branchName varchar(255); 
branchName2 varchar(255); 
fullBranchTitle varchar(255); 
ownerBankShortName varchar(255); 
ownerBankFullName varchar(255); 
ownerBankCode varchar(255); 
property varchar(255); 
street varchar(255); 
locality varchar(255); 
town varchar(255); 
county varchar(255); 
postcode varchar(255); 
stdCode varchar(255); 
phone varchar(255); 
faxStdCode varchar(255); 
fax varchar(255); 
countryIndicator varchar(255); 
reserved1 varchar(255); 
reserved2 varchar(255); 
reserved3 varchar(255); 
reserved4 varchar(255); 
reserved5 varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetBankRecord(recNo, sortCode, bankBIC, branchBIC, subBranchSuffix, shortBranchTitle, centralBankCountryCode, supervisoryBody, deletedDate, branchTypeIndicator, mainBranchSortCode, majorLocationName, minorLocationName, branchName, branchName2, fullBranchTitle, ownerBankShortName, ownerBankFullName, ownerBankCode, property, street, locality, town, county, postcode, stdCode, phone, faxStdCode, fax, countryIndicator, reserved1, reserved2, reserved3, reserved4, reserved5); 
-- parameters supplied to BFGetBankRecord now contain the general bank
-- fields so these can now be displayed or assigned to your database table. 
END;

BFGetBACSRecord

Applicable to UK (BACS) Records Only

Used to return bank details for the BACS clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord..

Declaration

				
					CREATE OR REPLACE PROCEDURE BFGetBACSRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
redirectedFromFlag OUT varchar2, redirectedToSortCode OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, settlementSection OUT varchar2,
settlementSubSection OUT varchar2, handlingBankCode OUT varchar2,
handlingBankShortName OUT varchar2, handlingBankFullName OUT varchar2,
handlingBankStream OUT varchar2, accountNumberedFlag OUT varchar2,
DDIVoucherFlag OUT varchar2, allowedDirectDebits OUT varchar2,
allowedBankGiroCredits OUT varchar2, allowedBuildingSocietyCredits OUT varchar2,
allowedDividendPayments OUT varchar2, allowedDirectDebitInstructions OUT varchar2,
allowedUnpaidChequeClaims OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBACSRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
redirectedFromFlag string, redirectedToSortCode string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, settlementSection string,
settlementSubSection string, handlingBankCode string,
handlingBankShortName string, handlingBankFullName string,
handlingBankStream string, accountNumberedFlag string,
DDIVoucherFlag string, allowedDirectDebits string,
allowedBankGiroCredits string, allowedBuildingSocietyCredits string,
allowedDividendPayments string, allowedDirectDebitInstructions string,
allowedUnpaidChequeClaims string);

Usage

				
					status varchar(255); 
lastChange varchar(255); 
closedClearing varchar(255); 
redirectedFromFlag varchar(255); 
redirectedToSortCode varchar(255); 
settlementBankCode varchar(255); 
settlementBankShortName varchar(255); 
settlementBankFullName varchar(255); 
settlementSection varchar(255); 
settlementSubSection varchar(255); 
handlingBankCode varchar(255); 
handlingBankShortName varchar(255); 
handlingBankFullName varchar(255); 
handlingBankStream varchar(255); 
accountNumberedFlag varchar(255); 
DDIVoucberFlag varchar(255); 
allowedDirectDebits varchar(255); 
allowedBankGiroCredits varchar(255); 
allowedBuildingSocietyCredits varchar(255); 
allowedDividendInterestPayments varchar(255); 
allowedDirectDebitInstructions varchar(255); 
allowedUnpaidChequeClaims varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has
-- already been called 
BFGetBACSRecord(status, lastChange, closedClearing, redirectedFromFlag, redirectedToSortCode, settlementBankCode, settlementBankShortName, settlementBankFullName, settlementSection, settlementSubSection, handlingBankCode, handlingBankShortName, handlingBankFullName, handlingBankStream, accountNumberedFlag, DDIVoucherFlag, allowedDirectDebits, allowedBankGiroCredits, allowedBuildingSocietyCredits, allowedDividendInterestPayments, allowedDirectDebitInstructions, allowedUnapidChequeClaims); 
-- parameters supplied to BFGetBACSRecord now contain the BACS fields
-- so these can now be displayed or assigned to your database table. 
END;

BFGetCHAPSRecord

Applicable to UK (BACS) Records Only

Used to return bank details for both the CHAPS Sterling and Euro clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord.

Declaration

				
					CREATE OR REPLACE PROCEDURE BFGetCHAPSRecord(
pStatus OUT varchar2, pLastChange OUT varchar2, pClosedClearing OUT varchar2, 
pSettlementBankCode OUT varchar2, pSettlementBankShortName OUT varchar2,
pSettlementBankFullName OUT varchar2,
eStatus OUT varchar2, eLastChange OUT varchar2, eClosedClearing OUT varchar2, 
eEuroRoutingBICBank OUT varchar2, eEuroRoutingBICBranch OUT varchar2,
eSettlementBankCode OUT varchar2, eSettlementBankShortName OUT varchar2,
eSettlementBankFullName OUT varchar2, eReturnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCHAPSRecord"
LANGUAGE C
PARAMETERS (pStatus string, pLastChange string, pClosedClearing string, 
pSettlementBankCode string, pSettlementBankShortName string,
pSettlementBankFullName string,
eStatus string, eLastChange string, eClosedClearing string, 
eEuroRoutingBICBank string, eEuroRoutingBICBranch string,
eSettlementBankCode string, eSettlementBankShortName string,
eSettlementBankFullName string, eReturnIndicator string);

Usage

				
					pStatus varchar(255); 
pLastChange varchar(255); 
pClosedClearing varchar(255); 
pSettlementBankCode varchar(255); 
pSettlementBankShortName varchar(255); 
pSettlementBankFullName varchar(255); 
eStatus varchar(255); 
eLastChange varchar(255); 
eClosedClearing varchar(255); 
eEuroRoutingBICBank varchar(255); 
eEuroRoutingBICBranch varchar(255); 
eSettlementBankCode varchar(255); 
eSettlementBankShortName varchar(255); 
eSettlementBankFullName varchar(255); 
eReturnIndicator varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetCHAPSRecord(pStatus, pLastChange, pClosedClearing, pSettlementBankCode, pSettlementBankShortName, pSettlementBankFullName, eStatus, eLastChange, eClosedClearing, eEuroRoutingBICBank, eEuroRoutingBICBranch, eSettlementBankCode, eSettlementBankShortName, eSettlementBankFullName, eReturnIndicator); 
-- parameters supplied to BFGetCHAPSRecord now contain the CHAPS fields
-- so these can now be displayed or assigned to your database table. 
END;

BFGetCCCCRecord

Applicable to UK (BACS) Records Only

Used to return bank details for both the C&CCC clearance system. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord..

Declaration

				
					CREATE OR REPLACE PROCEDURE BFGetCCCCRecord(
status OUT varchar2, lastChange OUT varchar2, closedClearing OUT varchar2, 
settlementBankCode OUT varchar2, settlementBankShortName OUT varchar2,
settlementBankFullName OUT varchar2, debitAgencySortCode OUT varchar2,
returnIndicator OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetCCCCRecord"
LANGUAGE C
PARAMETERS (status string, lastChange string, closedClearing string, 
settlementBankCode string, settlementBankShortName string,
settlementBankFullName string, debitAgencySortCode string,
returnIndicator string);

Usage

				
					status varchar(255); 
lastChange varchar(255); 
closedClearing varchar(255); 
settlementBankCode varchar(255); 
settlementBankShortName varchar(255); 
settlementBankFullName varchar(255); 
debitAgencySortCode varchar(255); 
returnIndicator varchar(255); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetCCCCRecord(status, lastChange, closedClearing, settlementBankCode, settlementBankShortName, settlementBankFullName, debitAgencySortCode, returnIndicator); 
-- parameters supplied to BFGetCCCCRecord now contain the C&CCC fields
-- so these can now be displayed or assigned to your database table. 
END;

BFGetBankLine

Used to get a line containing bank details following a call to BFGetFirst, BFGetNext or BFJumpToRecord. This line is formatted for list display and to provide for user selection.

Declaration

				
					CREATE OR REPLACE PROCEDURE BFGetBankLine(line OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankLine"
LANGUAGE C
PARAMETERS (line string);

Usage

				
					line varchar(512); 
BEGIN 
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called 
BFGetBankLine(line); 
-- line now contains the bank details line 
END;

Result

The bank line returned as a string for display. Normally you would provide the user with a list of these called from each iteration of a call to BFGetFirst and subsequent calls to BFGetNext for a lookup or search.

Oracle Integration Guide

Contents

Configure Oracle for Postcode Plus

Add the Functions

Using the API

Postcode Plus Examples

PCLookup

Oracle Forms

Postcode Plus Functions

GetPostcode

Declaration

Usage

Result

GetAddress

Declaration

Usage

Result

GetAddressLine

Declaration

Usage

Parameters

Result

GetFormattedAddress

Declaration

Usage

Parameters

GetLocality

Declaration

Usage

GetAddressRec

Declaration

Usage

FastFind

Declaration

Usage

Parameters

Result

InitiateSearch

Declaration

Usage

Result

GetMatch

Declaration

Usage

Result

LookupUdprn

Declaration

Usage

result

Configure Oracle for BankFinder

Add the Functions

Using the API

BankFinder Examples

BFLookup

Oracle Forms

Bankfinder Functions

BFGetFirst and BFGetNext

Declarations

Usage (1) – Lookup

Usage (2) – Search

Result

BFGetFirstRedirect and BFGetNextRedirect

Declarations

Usage

Result

BFJumpToRecord

Declaration

Usage

Result

BFValidateAccount

Declaration

Usage

Result

BFValidateCard

Declaration

Usage

Result

BFGetBankRecord

Declaration

Usage