Re: Circular Record Reference

From: Dave <davidr21_at_hotmail.com>
Date: 25 Mar 2004 20:21:26 -0800
Message-ID: <8244b794.0403252021.7aa3bc1d_at_posting.google.com>

forum_at_iicii.com (Paffko) wrote in message news:<885d6057.0403250924.1cb059f9_at_posting.google.com>...
> To: All Who Love Oracle Challenges
>
> Environment:
> Oracle8i (8.1.7)
>
> Scenario:
> The following records exist in a table:
>
> ITEM DEST SRC
> ---- ---- ----
> 1111 101 201
> 1111 201 202
> 1111 202 101
>
> or in a generic example, we have:
>
> ITEM DEST SRC
> ---- ---- ----
> 1111 A B
> 1111 B C
> 1111 C A
>
> Question:
> What would be the best approach in Oracle8i to find a circular
> reference in columns DEST and SRC? For example, for ITEM=1111, the
> following circular reference exists for DEST->SRC: A->B->C->A
>
> Any ideas are highly appreciated!
> Thanks.

Not sure its the best approach, but you could loop over each distinct item, run a hierarchical query for that item, catch the exception..."ORA-01436: CONNECT BY loop in user data" and then spit out the rows for this item.

Pseudo-code...

declare
 l_item number;
begin
  for c1 in ( select distinct item from tmp_table )   loop

      l_item := c1.item;
      SELECT item, dest, src
      FROM tmp
      where item = l_item
      CONNECT BY PRIOR dest = src;

  end loop;
exception
  when <Appropriate exception handling for ORA-01436> then     for c2 in ( select item, dest, src from tmp_table where item = l_item )

    loop
      DBMS_OUTPUT.PUT_LINE(c2.item||' '||c2.dest||' '||c2.src);     end loop;
  when others then raise;
end;
/ Received on Fri Mar 26 2004 - 05:21:26 CET