Link to home
Start Free TrialLog in
Avatar of bommii
bommii

asked on

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 :

CREATE OR REPLACE PROCEDURE name_match
      IS
      
            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;
            TYPE VARCHAR2_TT IS TABLE OF VARCHAR2(50);
            v_return_array VARCHAR2_TT := VARCHAR2_TT();
            v_text VARCHAR2(2000);
BEGIN

      INSERT INTO tmp_table
      SELECT * FROM source_table;

      DELETE from source_table;

      --CURSOR for name matching

      DECLARE CURSOR name_match is
            SELECT
                  primarykey,
                  target_name,
                  target_dob,
                  target_gender,
                  source_dob,
                  source_gender,
                  source_name
            FROM
                  tmp_table;

      BEGIN
      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
                  v_return_array.EXTEND;

                  -- 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);
                  ELSE
                        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||' ');
                        EXIT;
                  ELSE
                        -- 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
               values(name_rec.primarykey,
               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;

END;

END;
/

Iam very new to oracle, pls help.
Avatar of RCorfman
RCorfman

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;

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

To:
          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
Avatar of bommii

ASKER

Thanks,

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?
Avatar of bommii

ASKER

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||' ');
ASKER CERTIFIED SOLUTION
Avatar of RCorfman
RCorfman

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bommii

ASKER

Thanks for your help, i'll post the entire err msg and sample data once i get to office around 10AM singapore time
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...)
RC,

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.

sora