AFD Software: Address Management, Postcode and Bank solutions

PCLookup

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.