This procedure is supposed to scrub certain name strings(eg, JR, SR, |||, IV etc) in LAST_NAME Column in the source table
I created an Oracle table(similar to Array) which has all the exclusion strings. I am trying to run the procedure and load
another target table.THe procedure either deleted the entire string coming from source or desn’t delete at all.I have even
tried to create the second procedure(sp_name_token1)separately instead of calling it dynamically but still not
successful.Iam passing the procedure through an informatica mapping
CREATE OR REPLACE PROCEDURE sp_name_cleanup1(name_string IN OUT VARCHAR2)
IS
TYPE t_words IS TABLE OF OCBADM.NAME_EXCLUDE.TITLE_LIST%TYPE INDEX BY BINARY_INTEGER;
v_token t_words;
name_words t_words;
keywords t_words;
counter INTEGER:= 1;
i t_words;
name_in VARCHAR2(40);
CURSOR c1 IS (SELECT TITLE_LIST FROM OCBADM.NAME_EXCLUDE);
— Create procedure sp_token:
PROCEDURE sp_name_token1 (v_name_in IN OUT varchar2)
IS
BEGIN
FOR v_name_in IN c1
LOOP
IF c1%FOUND
THEN v_token.delete;
END IF;
END LOOP;
END;
BEGIN
FOR c1_rec in c1
LOOP
counter:= counter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘counter updated’);
sp_name_token1(name_in);
FOR i in 1..name_words.count
LOOP
for j in 1..keywords.count
loop
IF keywords(j) = name_words(i)
THEN
name_words.delete(j);
EXIT;
END IF;
end loop;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘DONE’);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘NO DELETES’);
END sp_name_cleanup1;