Continue...............

In my TAB_EX table I have the following records :

ID  NAME               COMP_NAME                 FLD1                                DISTRICT
--- ------------------ ------------------------- ----------------------------------- ---------------
1   Alan Border                                  1 Ada Street                        Salt Lake
2   Steve Bucknor                                1 A Kalinda Street                  Salt Lake
3   Tony Day                                     1/13 Burrawang Street               Salt Lake
4   Phil Hampson                                 1/21-23 Chelmsford Road             Salt Lake
5   John Reid                                    10/1a Frederick Street              Salt Lake
6   Steve Wright                                 161? Lang Street                    Salt Lake
7   Jason Gillespie                              368 Cnr Barker & Anzac Pde          Salt Lake
8   Greg Chapman                                 5/70-74/ Wilson Street              Salt Lake
9   Paul Radcliffe                               Shop 2 4-14 Claudia Road            Salt Lake
10  George Hemmingway                            PO Box 558                          Salt Lake
11  Dave Mathews                                 Lot 4 Mungerie Road                 Salt Lake
12  Jonty Rhodes                                 Level 4/65 Albert Avenue            Salt Lake
13  Clive Llyode                                 Cnr Warf Rd & Hollywood Rd          Salt Lake
14  John Davies                                  Unit 85 51 Little Willandra Rd      Salt Lake
15  Stephen Matthews                             Suite 2 Ground Floor                Salt Lake
16  Chris Harris                                 Street                              Salt Lake
17                     Anzac Solicitors          Burwood Road                        Salt Lake
18                     Joe & Joyce Travels       Paramatta Street                    Salt Lake
19  Ruth Hughes                                  Harmondsworth Street                Salt Lake
20                     Greenwood Technologies    Cranston Drive                      Salt Lake
21                     Haywire News Ltd          Martindale Road                     Salt Lake
22  Mark Jones                                   12 Ada Street                       Wellington
23  Peter Radcliffe                              14 Ada Street                       Bhubneshwar
24  Michael Chang                                15 Ada Street                       Kolkata
25  Chris Gayle                                  18 Ada Street                       Darjeeling
26  Peter Gibson                                 10a M'cac Street                    Salt Lake
27  Mike Atherton                                11 Up-town Road                     Salt Lake
28                     Ranbaxy Ltd               upper & lower Street                Salt Lake
29                     Glaxo Smith Cline         Hill Road & downhill Road           Salt Lake
30                     20th Century Fox          Dance Pde & mahal Street            Salt Lake

30 rows selected.


After executing the code I get the following records in TAB_EX1 :

ID  NAME               COMP_NAME                 ROADNUM         ROADNAME                  POSTCODE
--- ------------------ ------------------------- --------------- ------------------------- --------
1   Alan Border                                  1               Ada Street                123345
2   Steve Bucknor                                1               A Kalinda Street          123345
3   Tony Day                                     1/13            Burrawang Street          123345
4   Phil Hampson                                 1/21-23         Chelmsford Road           123345
5   John Reid                                    10/1a           Frederick Street          123345
6   Steve Wright                                 161             Lang Street               123345
8   Greg Chapman                                 5/70-74         Wilson Street             123345
9   Paul Radcliffe                               Shop 2 4-14     Claudia Road              123345
10  George Hemmingway                                            PO Box 558                123345
11  Dave Mathews                                 Lot 4           Mungerie Road             123345
12  Jonty Rhodes                                 Level 4/65      Albert Avenue             123345
14  John Davies                                  Unit 85 51      Little Willandra Rd       123345
17                     Anzac Solicitors                          Burwood Road              123345
18                     Joe & Joyce Travels                       Paramatta Street          123345
20                     Greenwood Technologies                    Cranston Drive            123345
21                     Haywire News Ltd                          Martindale Road           123345
24  Michael Chang                                15              Ada Street                123348
25  Chris Gayle                                  18              Ada Street                123350
26  Peter Gibson                                 10a             M'cac Street              123345
27  Mike Atherton                                11              Up-town Road              123345
28                     Ranbaxy Ltd                               upper & lower Street      123345
29                     Glaxo Smith Cline                         Hill Road & downhill Road 123345
30                     20th Century Fox                          Dance Pde & mahal Street  123345

23 rows selected.

And following records are inserted in Error table :

ID  NAME               COMP_NAME            FLD1                         DISTRICT      ERRMSG
--- ------------------ -------------------- ---------------------------- ------------- -------------
7   Jason Gillespie                         368 Cnr Barker & Anzac Pde   Salt Lake     Road Number not provided for personal address
13  Clive Llyode                            Cnr Warf Rd & Hollywood Rd   Salt Lake     Road Number not provided for personal address
15  Stephen Matthews                        Suite 2 Ground Floor         Salt Lake     Invalid Road Name
16  Chris Harris                            Street                       Salt Lake     Invalid address format
19  Ruth Hughes                             Harmondsworth Street         Salt Lake     Road Number not provided for personal address
22  Mark Jones                              12 Ada Street                Wellington    No Data Found for District in PINCODES table
23  Peter Radcliffe                         14 Ada Street                Bhubneshwar   No Data Found for District in PINCODES table

7 rows selected.

You may notice, that the addresses like M'cac Street, upper & lower Street, Hill Road & downhill Road and Dance Pde & mahal Street are validated properly, and are put in the right fields.

Following is the final code that I have executed :

--create or replace procedure proc_tab_ex as
declare
    v_roadno   varchar2(50);
    v_roadnm   varchar2(200);
    v_lastnum  varchar(100);
    v_tempnm   varchar2(200);
    v_addrtyp  varchar2(30);
    v_postcode varchar2(30);
    v_errmsg   varchar2(250);

    cursor c1 is
    select * from tab_ex;

    FUNCTION f_addr_type ( fulladdr varchar2)
    RETURN VARCHAR2 IS
     v_space VARCHAR2(30);
    BEGIN
        IF INSTR(fulladdr, 'Cnr') > 0 THEN
            v_space := 'Cnr';
        END IF;
        IF INSTR(fulladdr, 'Shop') > 0 THEN
            v_space := 'Shop';
        END IF;
        IF INSTR(fulladdr, 'PO Box') > 0 THEN
            v_space := 'PO Box';
        END IF;
        IF INSTR(fulladdr, 'Lot') > 0 THEN
            v_space := 'Lot';
        END IF;
        IF INSTR(fulladdr, 'Level') > 0 THEN
            v_space := 'Level';
        END IF;
        IF INSTR(fulladdr, 'Unit') > 0 THEN
            v_space := 'Unit';
        END IF;
        RETURN v_space;
    END f_addr_type;

    PROCEDURE p_pincode (i_district IN varchar2, o_postcode OUT varchar2, o_errmsg OUT varchar2) IS
     v_msg VARCHAR2(250);
    BEGIN
        BEGIN
            SELECT POSTCODE
            INTO   o_postcode
            FROM   tab_pins
            WHERE  DISTRICT = i_district
            AND STATE       = 'West Bengal'
            AND CATEGORY    = 'Courier Service'
            AND ROWNUM      = 1;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            o_errmsg:='No Data Found for District in PINCODES table';
        WHEN OTHERS THEN
            o_errmsg:='Others Exception raised for the district';
        END;
    END p_pincode;

    FUNCTION f_logerr (tabexrec tab_ex%rowtype, i_errmsg varchar2)
    RETURN VARCHAR2
    IS
    v_space VARCHAR2(30);
    BEGIN
        insert into tab_ex_err
        (ID, NAME, COMP_NAME, FLD1, DISTRICT, ERRMSG)
        values
        (tabexrec.id, tabexrec.NAME, tabexrec.COMP_NAME, tabexrec.FLD1, tabexrec.DISTRICT, i_errmsg);
        RETURN v_space;
    END f_logerr;


BEGIN -- Main Block
    for c1_rec in c1
    loop
        -- First check whether the District is valid (the one that we are looking for)
        IF c1_rec.DISTRICT IS NULL THEN
            v_errmsg := 'District is not provided';
            v_errmsg := f_logerr(c1_rec,v_errmsg);
            goto loop_next;
        ELSE
            p_pincode(c1_rec.district, v_postcode, v_errmsg);
            IF v_errmsg IS NOT NULL THEN
                v_errmsg := f_logerr(c1_rec,v_errmsg);
                goto loop_next;
            END IF;
        END IF;

        -- Check the format of the address
        v_addrtyp := f_addr_type (c1_rec.fld1);

        -- Case 1. "Cnr"
        --         ignore everything before "Cnr" and put the rest in the house/road name
        IF v_addrtyp = 'Cnr' THEN
            v_roadno := '';
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1, 'Cnr')+ 4);
            goto proc_next;
        END IF;

        -- Case 2. "Shop" -- Case 4. "Lot" -- Case 5. "Level" -- Case 6. "Unit"
        IF v_addrtyp IN ('Shop','Lot','Level','Unit','House') THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto proc_next;
        END IF;

        -- Case 3. "PO Box"
        --         Put everything in road name
        IF INSTR(c1_rec.fld1, 'PO Box') > 0 THEN
            v_roadno := '';
            v_roadnm := c1_rec.fld1;
            goto proc_next;
        END IF;

        -- If none of the above cases, then its the normal address.
        -- Find the last number in the string
        -- If none of the above cases, then its the normal address.
        v_roadno := substr(c1_rec.fld1, 1, instr(c1_rec.fld1, ' '));
        v_roadnm := substr(c1_rec.fld1, instr(c1_rec.fld1, ' '));

        v_lastnum := substr(trim(translate(upper(v_roadno),
                     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

        IF v_lastnum is null THEN
             v_roadno := NULL;
             v_roadnm := c1_rec.fld1;
        END IF;

/*
        v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
        v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
        v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
*/

        IF v_roadno IS NULL AND v_roadnm IS NULL THEN
            v_roadnm := c1_rec.fld1;
        END IF;

        <<proc_next>>
        v_roadno := trim(v_roadno);
        v_roadnm := trim(v_roadnm);
        -- last thing, check if the last character of road number is '/' or '?'. Strip if this is the case
        IF substr(v_roadno,-1) = '/' OR substr(v_roadno,-1) = '?' THEN
            v_roadno := substr(v_roadno,1,length(v_roadno)-1);
        END IF;

        IF instr(v_roadnm,' ') > 0 THEN
            -- If road name has more than one word then check if it has
            -- one of the valid values - Rd, Road, Avenue, Street, Pde or PO BOX
            IF instr(v_roadnm, 'Rd') > 0
            OR instr(v_roadnm, 'Road') > 0
            OR instr(v_roadnm, 'Avenue') > 0
            OR instr(v_roadnm, 'Ave') > 0
            OR instr(v_roadnm, 'Street') > 0
            OR instr(v_roadnm, 'Pde') > 0
            OR instr(v_roadnm, 'PO Box') > 0
            OR instr(v_roadnm, 'Drive') > 0
            OR instr(v_roadnm, 'Hwy') > 0
            THEN -- THIS IS A VALID RECORD. Insert into TAB_EX1

                -- If Road Number is null and company name does not exist (i.e., a personal address) then its invalid
                IF v_roadno IS NULL and c1_rec.comp_name IS NULL AND INSTR(v_roadnm, 'PO Box') <= 0 THEN
                    dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                    v_errmsg := 'Road Number not provided for personal address';
                    v_errmsg := f_logerr(c1_rec,v_errmsg);
                    goto loop_next;
                END IF;

                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm);

                INSERT INTO tab_ex1
                (ID, NAME, COMP_NAME, ROADNUM, ROADNAME, POSTCODE)
                VALUES
                (c1_rec.ID, c1_rec.NAME, c1_rec.COMP_NAME, v_roadno, v_roadnm, v_postcode);

            ELSE
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                v_errmsg := 'Invalid Road Name';
                v_errmsg := f_logerr(c1_rec,v_errmsg);
            END IF;
        ELSE
            dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
            v_errmsg := 'Invalid address format';
            v_errmsg := f_logerr(c1_rec,v_errmsg);
        END IF;

        <<err_rec>>
        NULL;

        <<loop_next>>
        NULL;
    end loop;
end;
/

Hope this will now work for you.

You may notice, that the addresses like M'cac Street, upper & lower Street, Hill Road & downhill Road and Dance Pde & mahal Street are validated properly, and are put in the right fields.

Just in case if it does'nt, then please give me the details of records that I should use to simulate the problems that you are getting.