Configure Oracle

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