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.targe t_name,p_d elimiter), p_delimite r);
--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_ne xt_positio n -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.so urce_name, v_return_array(j), v_return_array(j)||'~')) - length(name_rec.source_nam e) 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.
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.targe
--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_ne
v_text := RTRIM(SUBSTR(v_text,v_next
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.so
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.
ASKER
Thanks,
But iam getting the error, undeclared identifier 'v_return_array'
same error if i made the changes you suggested.
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?
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||' ');
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!!!
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
sora
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