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.
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.
Run the PCLookup function to check everything is working. You can then add the Postcode Plus functionality to your Oracle system using PL/SQL.
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.
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.
Used to carry out a Postcode lookup and retrieve the associated postcode locality information.
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);
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;
locRecs indicates number of postcode localities found for this lookup.
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.
Used to fetch the delivery point (address) data following a call to GetPostcode or GetMatch.
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);
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;
retVal indicates the success of the address call-off.
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.
CREATE OR REPLACE PROCEDURE GetAddressLine(
line OUT varchar2, countyType BINARY_INTEGER)
AS EXTERNAL LIBRARY pcplus
NAME "DoGetAddressLine"
LANGUAGE C
PARAMETERS (line string, countyType long);
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;
countyType
The type of counties you wish to display. The options for these are as follows:
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.
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.
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);
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;
countyType
The type of counties you wish to display. The options for these are as follows:
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.
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);
/
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).
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.
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);
/
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).
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’.
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);
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;
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.
retVal indicates the return value of the FastFind function.
Used to start an Address Search to find addresses matching your criteria.
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);
/
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.
retVal indicates the return value from the InitiateSearch function.
Used to return the associated postcode locality information following a Search or Fast Find.
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);
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;
ReturnType indicates the success of the address call-off:
Used to lookup an address record directly from it’s UDPRN code.
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);
/
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;
ReturnType indicates the success of the UDPRN lookup:
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)
)
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\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.
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.
Run the BFLookup function to check everything is working. You can then add the BankFinder functionality to your Oracle system using PL/SQL.
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.
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.
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 ‘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.
Used to carry out a Bank lookup or search and retrieve the matching branches.
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;
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;
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;
retVal indicates the return value of the BFGetFirst/BFGetNext function:
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.
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);
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;
retVal indicates the return value of the BFGetFirstRedirect/BFGetNextRedirect function:
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.
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);
recNo integer;
countyType integer;
BEGIN
recNo := -- Record Number you wish to jump back too
retVal := BFJumpToRecord(recNo);
-- If retVal = 0 then successful jump
END;
retVal indicates the return value of the BFJumpToRecord function:
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.
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);
/
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;
retVal indicates the return value of the BFValidateAccount function:
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.
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);
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;
retVal indicates the return value of the BFValidateCard function:
Returns Card Type (>0) if success:
Returns <0 if fails:
Used to return general bank details. This should be called after a call to BFGetFirst, BFGetNext or BFJumpToRecord.
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);
/
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;
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..
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);
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;
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.
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);
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;
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..
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);
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;
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.
CREATE OR REPLACE PROCEDURE BFGetBankLine(line OUT varchar2)
AS EXTERNAL LIBRARY afdbank
NAME "DoGetBankLine"
LANGUAGE C
PARAMETERS (line string);
line varchar(512);
BEGIN
-- This assumes BFGetFirst, BFGetNext or BFJumpToRecord has already
-- been called
BFGetBankLine(line);
-- line now contains the bank details line
END;
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.