Problem with Arrays

I have written a procedure and iam also able to compile it, but while running this procedure iam getting an error that v_return_array is undefined.
I have a table with 2 name columns. I need to compare the columns and keep only the rows where atleast 2 words in the name match.
example "David Smith"  & "David John Smith" is a match
while "David Smith" & "David Plunket" is not a match

The procedure i have written is below :

            i PLS_INTEGER := 1;
            j PLS_INTEGER := 1;
            v_word_match PLS_INTEGER;
            v_match_count PLS_INTEGER := 0;
            v_next_position PLS_INTEGER;
            v_enclosed BOOLEAN;
            p_delimiter VARCHAR2(1) := ' ';
            p_encloser VARCHAR2(10) := NULL;
            v_return_array VARCHAR2_TT := VARCHAR2_TT();
            v_text VARCHAR2(2000);

      INSERT INTO tmp_table
      SELECT * FROM source_table;

      DELETE from source_table;

      --CURSOR for name matching

      DECLARE CURSOR name_match is

      FOR name_rec IN name_match LOOP

      v_text := LTRIM(RTRIM(name_rec.target_name,p_delimiter),p_delimiter);

      --LOOP to split the target name
            WHILE v_text IS NOT NULL LOOP

                  -- Check whether (remaining) text starts with an encloser (e.g. doublequote):
                  v_enclosed := SUBSTR(v_text ,1,1) = p_encloser;

                  IF v_enclosed THEN
                        v_text := RTRIM(SUBSTR(v_text,2));
                        v_next_position := INSTR(v_text,p_encloser);
                        v_next_position := INSTR(v_text,p_delimiter);
                  END IF;

                  IF v_next_position = 0 THEN
                        -- No more delimiters found, so return remaining text
                        -- (first strip off any spaces and enclosing quotes)
                        v_return_array(i) := RTRIM(v_text, p_encloser||' ');
                        -- Use the portion of the text up to the next delimiter or encloser:
                        -- ('v_next_position' has already been worked out)
                        v_return_array(i) := RTRIM(SUBSTR(v_text,1,v_next_position -1));
                        v_text := RTRIM(SUBSTR(v_text,v_next_position +1));

                        IF v_enclosed THEN
                              -- Deal with closing quote by stripping one more character:
                              v_text := RTRIM(SUBSTR(v_text,2));
                        END IF;
                  END IF;

                  i := i + 1;
            END LOOP;

      --LOOP to check if each word in the target name is found in the source name

            FOR  j in 1..i LOOP

                  -- SELECT v_word_match into length(replace(name_rec.source_name, v_return_array(j), v_return_array(j)||'~')) - length(name_rec.source_name) from dual;

                  IF v_word_match > 0 Then

                        v_match_count := v_match_count + 1;

                  END IF;

            END LOOP;

            IF v_match_count > 1 then

               v_match_count := 0;

               INSERT INTO source_table
               name_rec.target_name, name_rec.target_dob, name_rec.target_gender,
               name_rec.source_dob, name_rec.source_gender, name_rec.source_name);

            END IF;

            v_match_count := 0;
            i := 0;
      --      v_return_array.EMPTY;
      END LOOP;



Iam very new to oracle, pls help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm not sure exactly what you are trying to do, but the routine did compile and run for me, then as I tried more data, I got it to fail, but it was with a subscript problem...
At the end of the loop, you set i:=0... that doesn't work, I changed it to set i:=1;

          v_match_count := 0;
          i := 0;
     --     v_return_array.EMPTY;
     END LOOP;

          v_match_count := 0;
          i := 1;
     --     v_return_array.EMPTY;
     END LOOP;

The error I was getting was:
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "MYSCHEMA.NAME_MATCH", line 64
ORA-06512: at line 1
bommiiAuthor Commented:

But iam getting the error, undeclared identifier 'v_return_array'
same error if i made the changes you suggested.
on which line?  Do you have a very small set of sample data that causes the error... like 4-6 that will cause the error and I can try to replicate it?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bommiiAuthor Commented:
The errorwas not because of the data.
I got the error when i try to assign value to the array

v_return_array(i) := RTRIM(v_text, p_encloser||' ');
Please show the entire error message. I ran the procedure with a few rows of data that errored at line 64, which is also trying to assign to the array, just in a different procedure branch
   v_return_array(i) := RTRIM(SUBSTR(v_text,1,v_next_position -1));
Fixing the subscript fixed that error.  The data you are processing must be causing different branches that are failing, OR, the procedure has changed.
---------- ---------- --------- - --------- - ----------
         1 Rob Corf   15-APR-06 M 15-APR-06 M Rob Corf
         1 Rob A Corf 15-APR-06 M 15-APR-06 M Rob A Corf
         1 Der Corf   15-APR-06 F 15-APR-06 F Der Corf
This is the subset of data that I was using.

I just added additional data and it does not fail. If you can narrow your data down to a very small set of data that actually fails, and be sure you are using the posted procedure, I can provide additional assistance.
SQL> select * from source_table;

---------- ---------- --------- - --------- - ----------
         1 Rob Corf   16-APR-06 M 16-APR-06 M Rob Corf
         1 Der Corf   16-APR-06 F 16-APR-06 F Der Corf
         1 Rob A Corf 16-APR-06 M 16-APR-06 M Rob A Corf
         1 Rob A Corf 16-APR-06 M 16-APR-06 M Rob Korf
         1 "R A" Corf 16-APR-06 M 16-APR-06 M Rob Korf

SQL> exec name_match;

PL/SQL procedure successfully completed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bommiiAuthor Commented:
Thanks for your help, i'll post the entire err msg and sample data once i get to office around 10AM singapore time
slightwv (䄆 Netminder) Commented:
I have no doubt that RCorfman will be able to help you work out the bugs but if I understand the problem, I'm thinking it can be a little cleaner.  No temp table or arrays.

drop table tab1;
create table tab1(name1 varchar2(50), name2 varchar2(50));

insert into tab1 values('David John Smith', 'David Smith');
insert into tab1 values('  David Smith  ', 'David John Smith');
insert into tab1 values('"David Smith"', 'David Smith');
insert into tab1 values('David Plunket', 'David Smith');
insert into tab1 values('Fred "The Killer" Flintstone', 'Fred Flintstone');
insert into tab1 values('Fred Flintstone', 'Fred "The Killer" Flintstone');
insert into tab1 values('Fred "Killer" Flintstone', 'Fred "The Killer" Flintstone');
insert into tab1 values('Fred Flintstone', 'Barney "I wish I was Fred" Rubble');

      mySQL varchar2(32000);

      for i in (select rowid, t1.* from tab1 t1) loop
            -- remove multiple spaces
            i.name1 := trim(replace(replace(replace(i.name1,'    ',' '),'   ',' '), '  ',' '));
            i.name2 := trim(replace(replace(replace(i.name2,'    ',' '),'   ',' '), '  ',' '));

            -- strip out any 'special' characters leaving only alpha
            i.name1 := translate(lower(i.name1),' abcdefghijklmnopqrstuvwxyz!@#$%^&*()"',' abcdefghijklmnopqrstuvwxyz');
            i.name2 := translate(lower(i.name2),' abcdefghijklmnopqrstuvwxyz!@#$%^&*()"',' abcdefghijklmnopqrstuvwxyz');

            mySQL := 'delete from tab1 where rowid in ( select ''' || i.rowid || ''' from dual where ( instr(''' || i.name2 || ''',''' ||
                  replace(i.name1,' ',''') = 0 or instr(''' || i.name2 || ''',''') || ''') = 0 )' ||
                        ' and  ( instr(''' || i.name1 || ''',''' ||
                  replace(i.name2,' ',''') = 0 or instr(''' || i.name1 || ''',''') || ''') = 0))';

            execute immediate mySQL;

      end loop;


select * from tab1;
I concur with slightwv, it is better if you can just do this as a straight cleanup of the data in-place.  The algorightms are different though, which may or may not be ok. It looked like you were treating data in "s as the 'same word' including spaces, at first glance (which may be wrong), it seems that this isn't necessarily the case with the alternative.... though I do concur this is a better approach, I just wasn't prepared to rewrite the routine (I'm on vacation with the family right now...)
slightwv (䄆 Netminder) Commented:

On vacation???  Is EE that addictive?  We'll be here when you get back.  Go enjoy yourself!!!
excellent suggestions above. if you are still curious why you get the error, I think it might be to do with the version you are using - I have 8i, 9i and 10g to try on. which version do you get the error on? I know pl/sql which works fine on 8i errors out in 9i and vice versa.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.