1 2 Previous Next 15 Replies Latest reply on Jun 4, 2012 9:28 AM by 894770

    Circular Dependency in data

    894770
      Im usiing oracle 9i.

      I have the following table

      create table JOB_DEPENDS
      (
      JOB VARCHAR2(40) not null,
      SUBJOB VARCHAR2(40) not null,
      DEP_JOB VARCHAR2(40) not null,
      DEP_SUBJOB VARCHAR2(40) not null
      );


      sample data:-

      insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
      values ('A', 'SA', 'B', 'SB');

      insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
      values ('B', 'SB', 'C', 'SC');

      insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
      values ('C', 'SC', 'A', 'SA');

      insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
      values ('D', 'SD', 'E', 'SE');

      Commit;


      Based on he above data

      Job A depends on job B to run.
      Job B depends on job C to run.
      Job C depends on job A to run.

      Thus there is circular dependency between the data A->B->C->A.


      I need a sql query to identify the circular dependent data.

      Output of the sql query should be like below

      JOB     SUBJOB     DEP_JOB     DEP_SUBJOB
      A     SA     B     SB
      B     SB     C     SC
      C     SC     A     SA

      In the above example circular dependency is between 3 jobs.

      I need an sql which finds circular dependency between any number of jobs


      Thanks in advance
        • 1. Re: Circular Dependency in data
          indra budiantho
          /* Formatted on 2012/06/01 15:35 (Formatter Plus v4.8.8) */
          SELECT     *
                FROM job_depends
          START WITH job = 'A'
          CONNECT BY NOCYCLE PRIOR subjob = dep_subjob
          JOB     SUBJOB     DEP_JOB     DEP_SUBJOB

          A     SA     B     SB
          C     SC     A     SA
          B     SB     C     SC
          • 2. Re: Circular Dependency in data
            AlbertoFaenza
            First of all thanks for posting table creation and data insertion.

            I'm afraid that with a single query you cannot find circular reference.

            You can only use the keywork NOCYCLE after connect by to avoid problem with circular references. If you don't specify this keyword when a circular reference is found the error -01436 is throws.

            If you want to find ONLY records which have circular reference you should do with some PL/SQL which might have performance issues on big tables.

            Here below an example:
            SET SERVEROUTPUT ON SIZE UNLIMITED
            
            DECLARE
               e_infinite_loop  EXCEPTION;
               PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);
               l_count          INTEGER;
            BEGIN
               FOR c1 IN (SELECT job FROM job_depends)
               LOOP
                  BEGIN
                     SELECT COUNT(*)
                       INTO l_count
                       FROM job_depends j
                      START WITH job = c1.job
                    CONNECT BY job = PRIOR dep_job;
                  EXCEPTION
                     WHEN e_infinite_loop
                     THEN
                        DBMS_OUTPUT.PUT_LINE('Infinite loop detected on Job: '||c1.job);
                  END;
               END LOOP;
            END;
            / 
            
            Infinite loop detected on Job: A
            Infinite loop detected on Job: B
            Infinite loop detected on Job: C
            {code}
            
            Regards
            Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
            • 3. Re: Circular Dependency in data
              Nicosa-Oracle
              Hi,

              You might use the NOCYCLE to create all paths, then join it to the original table and check if new path starts and ends with the same job :
              Scott@my11g SQL>!cat q.sql
              with JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
              as (
                   select 'A', 'SA', 'B', 'SB' from dual
                   union all select 'B', 'SB', 'C', 'SC' from dual
                   union all select 'C', 'SC', 'A', 'SA' from dual
                   union all select 'D', 'SD', 'E', 'SE' from dual
                   union all select 'E', 'SE', 'F', 'SF' from dual
                   union all select 'G', 'SG', 'A', 'SA' from dual
                   )
              ------ end of sample data ------
              select *
              from (
                   select v.*, replace(v.pth||':'||t.dep_job,':') npth
                   from (
                        SELECT SYS_CONNECT_BY_PATH(job,':') pth, subjob
                              FROM job_depends
                        CONNECT BY NOCYCLE subjob = PRIOR dep_subjob
                   ) v
                   join job_depends t
                   on (v.subjob=t.subjob)
              )
              where substr(npth,1,1)=substr(npth,-1)
              /
              
              Scott@my11g SQL>@q
              
              PTH                            SU NPTH
              ------------------------------ -- ------------------------------
              :A:B:C                         SC ABCA
              :B:C:A                         SA BCAB
              :C:A:B                         SB CABC
              This assume that job will only be 1 character long. You might have to adapt the query according to what your job value could be.
              • 4. Re: Circular Dependency in data
                894770
                Hi

                Im using oracle 9i

                Nocycle is not supported in Oracle 9i


                Thanks
                • 5. Re: Circular Dependency in data
                  AlbertoFaenza
                  In my previous example I'm not using NOCYCLE.

                  I just mentioned that you could use NOCYCLE but I had forgot you are in Oracle 9i, so definitely you can just catch the exception.
                  I don't see other solutions.
                  Maybe someone else can advice.

                  Regards.
                  Al
                  • 6. Re: Circular Dependency in data
                    Nicosa-Oracle
                    891767 wrote:
                    Nocycle is not supported in Oracle 9i
                    Awwww..... Sorry, I checked for sys_connect_by_path, but didn't for NOCYCLE.

                    I'll keep trying to find a solution.
                    • 7. Re: Circular Dependency in data
                      indra budiantho
                      hi, try this, but i found in a link: http://laurentschneider.com/wordpress/2008/09/cycling.html
                      /* Formatted on 2012/06/01 16:50 (Formatter Plus v4.8.8) */
                      SELECT     LEVEL, a.*
                            FROM job_depends a
                           WHERE LEVEL <= 3
                      START WITH job = 'A'
                      CONNECT BY PRIOR subjob = dep_subjob AND LEVEL <= 3 AND PRIOR DBMS_RANDOM.VALUE != DBMS_RANDOM.VALUE;
                      • 8. Re: Circular Dependency in data
                        Nicosa-Oracle
                        Alberto Faenza wrote:
                        I don't see other solutions.
                        Maybe someone else can advice.
                        Your solution is nice.

                        Variation on it : one can create a function that returns 1 if the job is involved in a cycling chain (else return 0).
                        Scott@my11g SQL>!cat q.sql
                        create table job_depends
                        as select * from
                        (
                                select 'A' JOB, 'SA' SUBJOB, 'B' DEP_JOB, 'SB' DEP_SUBJOB from dual
                                union all select 'B', 'SB', 'C', 'SC' from dual
                                union all select 'C', 'SC', 'A', 'SA' from dual
                                union all select 'D', 'SD', 'E', 'SE' from dual
                                union all select 'E', 'SE', 'F', 'SF' from dual
                                union all select 'G', 'SG', 'A', 'SA' from dual
                        );
                        
                        create or replace function isCycling(p_startval varchar2)
                        return integer
                        is
                             e_infinite_loop  EXCEPTION;
                             PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);
                             l_count          INTEGER;
                        begin
                             SELECT COUNT(*)
                             INTO l_count
                             FROM job_depends j
                             START WITH job = p_startval
                             CONNECT BY subjob = PRIOR dep_subjob;
                             return 0;
                        EXCEPTION
                             WHEN e_infinite_loop
                             THEN
                             return 1;
                        END;
                        /
                        show errors
                        
                        select job
                        from job_depends
                        where isCycling(job)=1
                        /
                        
                        Scott@my11g SQL>@q
                        
                        Table created.
                        
                        
                        Function created.
                        
                        No errors.
                        
                        J
                        -
                        A
                        B
                        C
                        G
                        • 9. Re: Circular Dependency in data
                          AlbertoFaenza
                          Hi Nicosa,

                          that's true. With the function is even better.
                          Well done.

                          Regards.
                          Al
                          • 10. Re: Circular Dependency in data
                            894770
                            Hi Al,


                            I want the ouptut in this format

                            Infinite loop detected on Job: A SA B SB
                            Infinite loop detected on Job: B SB C SC
                            Infinite loop detected on Job: C SC A SA
                            • 11. Re: Circular Dependency in data
                              AlbertoFaenza
                              Hi,

                              it is not so difficult to change the query in this way:
                              SET SERVEROUTPUT ON SIZE UNLIMITED
                              
                              DECLARE
                                 e_infinite_loop  EXCEPTION;
                                 PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);
                                 l_count          INTEGER;
                              BEGIN
                                 FOR c1 IN (SELECT job, subjob, dep_job, dep_subjob FROM job_depends)
                                 LOOP
                                    BEGIN
                                       SELECT COUNT(*)
                                         INTO l_count
                                         FROM job_depends j
                                        START WITH job = c1.job
                                      CONNECT BY job = PRIOR dep_job;
                                    EXCEPTION
                                       WHEN e_infinite_loop
                                       THEN
                                          DBMS_OUTPUT.PUT_LINE('Infinite loop detected on Job: '|| c1.job ||' '|| c1.subjob ||' '|| c1.dep_job ||' '|| c1.dep_subjob);
                                    END;
                                 END LOOP;
                              END;
                              / 
                              
                              
                              Infinite loop detected on Job: A SA B SB
                              Infinite loop detected on Job: B SB C SC
                              Infinite loop detected on Job: C SC A SA
                              {code}
                              
                              Alternatively you can consider Nicosa solution (creating a new function isCycling) and write:
                              
                              {code:sql}
                              select *
                              from job_depends
                              where isCycling(job)=1
                              {code}
                              
                              Regards.
                              Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                              • 12. Re: Circular Dependency in data
                                894770
                                Hi Al,

                                Thanks for ur reply

                                If the data is as below

                                delete from job_depends;

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('A', 'SA', 'B', 'SB');

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('B', 'SB', 'C', 'SC');

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('C', 'SC1', 'A', 'SA');

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('D', 'SD', 'E', 'SE');

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('C', 'SC', 'C', 'SC1');

                                insert into JOB_DEPENDS (JOB, SUBJOB, DEP_JOB, DEP_SUBJOB)
                                values ('C', 'SC', 'C', 'SC2');

                                commit;


                                The output should be like below

                                Infinite loop detected on Job: A SA B SB
                                Infinite loop detected on Job: B SB C SC
                                Infinite loop detected on Job: C SC1 A SA
                                Infinite loop detected on Job: C SC C SC1

                                it should not have "Infinite loop detected on Job: C SC C SC2" in the ouput.

                                Since it will break the cycle.
                                • 13. Re: Circular Dependency in data
                                  chris227
                                  Hi,

                                  1. if you propose it, you can explain it?
                                  2. i would not rely on that: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3181424400346795479

                                  Regards
                                  • 14. Re: Circular Dependency in data
                                    AlbertoFaenza
                                    Hi,

                                    Sorry but I was traveling this afternoon and I could not reply immediately.

                                    You did not explain in the first post that the relation is with job AND subjob with dep_job and dep_subjob.
                                    Based on he above data

                                    Job A depends on job B to run.
                                    Job B depends on job C to run.
                                    Job C depends on job A to run.
                                    Sorry but my crystal ball has broken for longtime :-)

                                    Anyway I have 2 assumptions that you have to confirm:

                                    a) No duplicate records (same job, subjob, dep_job, dep_subjob)
                                    b) Hierarchical relation among records is job=dep_job AND subjob=dep_subjob

                                    If these assumptions are correct here below is the modifed script:
                                    SET SERVEROUTPUT ON SIZE UNLIMITED
                                    
                                    DECLARE
                                       e_infinite_loop  EXCEPTION;
                                       PRAGMA EXCEPTION_INIT (e_infinite_loop, -01436);
                                       l_count          INTEGER;
                                    BEGIN
                                       FOR c1 IN (SELECT job, subjob, dep_job, dep_subjob FROM job_depends)
                                       LOOP
                                          BEGIN
                                             SELECT COUNT(*)
                                               INTO l_count
                                               FROM job_depends j
                                              START WITH job = c1.job AND subjob=c1.subjob 
                                                     AND dep_job=c1.dep_job AND dep_subjob=c1.dep_subjob
                                            CONNECT  BY job = PRIOR dep_job AND subjob = PRIOR dep_subjob;
                                          EXCEPTION
                                             WHEN e_infinite_loop
                                             THEN
                                                DBMS_OUTPUT.PUT_LINE('Infinite loop detected on Job: '|| c1.job ||' '|| c1.subjob ||' '|| c1.dep_job ||' '|| c1.dep_subjob);
                                          END;
                                       END LOOP;
                                    END;
                                    / 
                                    
                                    and the output is:
                                    
                                    Infinite loop detected on Job: A SA B SB
                                    Infinite loop detected on Job: B SB C SC
                                    Infinite loop detected on Job: C SC1 A SA
                                    Infinite loop detected on Job: C SC C SC1
                                    
                                    {code}
                                    
                                    Regards
                                    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                    1 2 Previous Next