PL/SQL Snippet going to a infinite loop

[Samrat Som]

I have problem in PL/SQL
Let me tell you , there is a table and I want to do the matching algorithm

Table Name : uf_sam_reassign_match
OLD_ID NEW_ID
1 1
2 1
2 2
3 2
4 1
4 3

Desired output should be in the table uf_sam_reassign_match_actual1

OLD_ID NEW_ID
1 1
2 2
4 3


The PL/SQL Snippet for the same is

set serveroutput on size unlimited;


/* Formatted on 2013/03/22 20:45 (Formatter Plus v4.8.8) */
DECLARE
   CURSOR c
   IS
      SELECT DISTINCT old_id AS old_id
                 FROM uf_sam_reassign_match;

   a          c%ROWTYPE;

   CURSOR d (oldid IN uf_sam_reassign_match.old_id%TYPE)
   IS
      SELECT old_id, new_id
        FROM uf_sam_reassign_match
       WHERE old_id = oldid;

   v_old_id   VARCHAR2 (200);
   v_new_id   VARCHAR2 (200);
   v_cnt number;
   
BEGIN
   OPEN c;

   LOOP
      FETCH c
       INTO a;
      EXIT WHEN c%NOTFOUND;

      OPEN d (a.old_id);
      

      dbms_output.put_line('OLD_OD-' || a.old_id);
      LOOP
         FETCH d
          INTO v_old_id, v_new_id;

         c
         
         
         dbms_output.put_line('old_id ::' || v_old_id ||'  '|| 'new_id:'||v_new_id);
            SELECT COUNT (1)
              INTO v_cnt
              FROM uf_sam_reassign_match_actual1
             WHERE old_id = v_old_id OR new_id = v_new_id;
             
              

            IF v_cnt = 0
            THEN
               INSERT INTO uf_sam_reassign_match_actual1
                    VALUES (v_old_id, v_new_id);
               COMMIT;
               EXIT;
            END IF;
                      
                      
      END LOOP;

      CLOSE d;
   END LOOP;

   CLOSE c;
END;
/

But somehow at the row 4 of table uf_sam_reassign_match mentioned below its going to a infinite loop

Old_Id New_id
3 2


Please help me here.
I dont have any clue why its going to a infinite loop.


Thanks in Advance.

[chris webster]

I've put this post in the Oracle forum as you may get more responses from Oracle people there. Also I formatted your code to make it easier to read.

[chris webster]

In your inner loop on cursor d (you need to improve your naming conventions here), are you sure that the exit condition will always occur?

More generally, you would probably find it a lot easier to use a cursor FOR loop here. Here's a couple of examples based on your SQL:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  CURSOR c_src
  IS
    SELECT old_id,
           new_id
    FROM   uf_sam_reassign_match
    ORDER BY old_id,
      new_id;
BEGIN
  -- One way to do a cursor FOR loop
  FOR c1 IN c_src
  LOOP
    dbms_output.put_line('CURSOR FOR LOOP (1): Old ID = '||c1.old_id||
                                 ', New ID = '||c1.new_id);
  END LOOP;
  -- Another way to do a cursor FOR loop
  FOR c2 IN  (SELECT old_id, new_id FROM uf_sam_reassign_match ORDER BY old_id, new_id)
  LOOP
    dbms_output.put_line('CURSOR FOR LOOP (2): Old ID = '||c2.old_id||
                                ', New ID = '||c2.new_id);
  END LOOP;
END;

[chris webster]

If I understand your matching rule correctly, then you don't need two cursors or the nested loops here. You can use the data from your source table directly e.g. via a cursor FOR loop as shown above, provided you get the data in the right order. Then you want to insert each record into your target table in turn, but only if the NEW_ID does not already exist there.

Here is one way to do this kind of INSERT e.g. imagine that v_old_id and v_new_id are local variables in your PL/SQL code, and v_old_id = 3 and v_new_id = 2. What do you think will happen if a record already exists in the target table with NEW_ID = 2?

    INSERT INTO uf_sam_reassign_match_actual1 (old_id, new_id)
    SELECT v_old_id, v_new_id
    FROM dual
    WHERE NOT EXISTS
      (SELECT 1 
         FROM uf_sam_reassign_match_actual1 t1 
        WHERE t1.new_id = v_new_id );

There are other ways to do this, but I reckon this should give you everything you need to implement your procedure.

[Samrat Som]

Hi Chris,

Thanks a lot for takingout your precious time to resolve this issue.
actually your suggestion of forlop of cursor is good and readable.

there was an issue with my snippet and i have found it.
Basically after

Fetch d into v_old_id,v_new_id;
exit when d%notfound; -----> This was missing and therefore it was going to infinite loop

[chris webster]

Samrat Som wrote:exit when d%notfound; -----> This was missing and therefore it was going to infinite loop

Correct. That's why it's usually much easier to use a cursor FOR loop when you can: less hand-written code = less hand-written bugs!