[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

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.
0
bommii
Asked:
bommii
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
RCorfmanCommented:
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
0
 
bommiiAuthor Commented:
Thanks,

But iam getting the error, undeclared identifier 'v_return_array'
same error if i made the changes you suggested.
0
 
RCorfmanCommented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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||' ');
0
 
RCorfmanCommented:
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.
PRIMARYKEY TARGET_NAM TARGET_DO T SOURCE_DO S SOURCE_NAM
---------- ---------- --------- - --------- - ----------
         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;

PRIMARYKEY TARGET_NAM TARGET_DO T SOURCE_DO S SOURCE_NAM
---------- ---------- --------- - --------- - ----------
         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.

SQL>
0
 
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
0
 
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');
commit;

declare
      mySQL varchar2(32000);
begin

      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;
            commit;

      end loop;

end;
/

select * from tab1;
0
 
RCorfmanCommented:
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...)
0
 
slightwv (䄆 Netminder) Commented:
RC,

On vacation???  Is EE that addictive?  We'll be here when you get back.  Go enjoy yourself!!!
0
 
soraCommented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now