This discussion is archived
2 Replies Latest reply: May 1, 2013 2:59 AM by 965352 RSS

Problem in the Procedure GET_MATCHING

965352 Newbie
Currently Being Moderated
THE PROCEDURE JOB : GET compare BETWEEN ANY WORD IN TABLE dynamic for Each character but the loop not stop when i run the procedure
please help me i have frustration ...thanks for all team support

BODY Procedure :

CREATE OR REPLACE PROCEDURE APPS.get_matching
IS
CURSOR parent_data_cur
IS
SELECT *
FROM xxx_match_people;

CURSOR child_data_cur
IS
SELECT *
FROM xxx_match_people;

v_start NUMBER := 1;
v_end NUMBER := 1;
v_parent_name_substr VARCHAR2(100);
v_child_name_substr VARCHAR2(100);
BEGIN
FOR i_rec IN parent_data_cur
LOOP
FOR j_rec IN child_data_cur
LOOP
IF i_rec.ID <> j_rec.ID
THEN
IF LENGTH (TRIM (i_rec.NAME)) = LENGTH (TRIM (j_rec.NAME))
THEN
LOOP
v_parent_name_substr := SUBSTR (TRIM (i_rec.NAME), v_start, v_end);
v_child_name_substr := SUBSTR (TRIM (j_rec.NAME), v_start, v_end);

IF v_parent_name_substr = v_child_name_substr
OR ( v_parent_name_substr IN ('?', '?') AND v_child_name_substr IN ('?', '?')
OR v_parent_name_substr IN ('??', '??') AND v_child_name_substr IN ('??', '??')
OR v_parent_name_substr IN ('?', '?') AND v_child_name_substr IN ('?', '?')
OR v_parent_name_substr IN ('?', '?') AND v_child_name_substr IN ('?', '?')

)
THEN
DBMS_OUTPUT.put_line
( 'Character no. '
|| v_start
|| ' value '
|| v_parent_name_substr
|| ' in parent cursor = character no. '
|| v_start
|| ' value '
|| v_child_name_substr
|| ' in child cursor'
);
v_start := v_start + 1;
END IF;

EXIT WHEN v_start > LENGTH (TRIM (j_rec.NAME));
END LOOP;
END IF;
END IF;
END LOOP;
END LOOP;
END get_matching;
/
  • 1. Re: Problem in the Procedure GET_MATCHING
    965352 Newbie
    Currently Being Moderated
    the same Example : On Table Per_all_people_f

    CREATE OR REPLACE PROCEDURE APPS.get_matching
    IS
    CURSOR parent_data_cur
    IS
    SELECT *
    FROM per_all_people_f;

    CURSOR child_data_cur
    IS
    SELECT *
    FROM per_all_people_f;

    v_start NUMBER := 0;
    v_end NUMBER := 1;
    v_parent_name_substr VARCHAR2(100);
    v_child_name_substr VARCHAR2(100);
    BEGIN
    FOR i_rec IN parent_data_cur
    LOOP
    FOR j_rec IN child_data_cur
    LOOP
    IF i_rec.PERSON_ID <> j_rec.PERSON_ID
    THEN
    IF LENGTH (TRIM (i_rec.FIRST_NAME)) = LENGTH (TRIM (j_rec.FIRST_NAME))


    THEN
    Loop
    v_parent_name_substr := SUBSTR (TRIM (i_rec.FIRST_NAME), v_start, v_end);
    v_child_name_substr := SUBSTR (TRIM (j_rec.FIRST_NAME), v_start, v_end);

    IF v_parent_name_substr = v_child_name_substr
    OR ( v_parent_name_substr IN ('A', 'A') AND v_child_name_substr IN ('M', 'M')
    )


    THEN
    DBMS_OUTPUT.put_line
    ( 'Character no. '
    || v_start
    || ' value '
    || v_parent_name_substr
    || ' in parent cursor = character no. '
    || v_start
    || ' value '
    || v_child_name_substr
    || ' in child cursor'
    );

    v_start := v_start + 1;




    END IF;

    EXIT WHEN v_start > LENGTH (TRIM (j_rec.FIRST_NAME));
    END LOOP;
    END IF;
    END IF;
    END LOOP;
    END LOOP;
    END get_matching;
    /
  • 2. Re: Problem in the Procedure GET_MATCHING
    965352 Newbie
    Currently Being Moderated
    THE REPLAY FROM ME OTHER Example for table HR

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points