Parent child relationship oracle - Struggling to pass with the solutions found

how did you get 3 levels of tables from dba_constraints?  the foreign keys only have 2 levels


also, you have circular references  such as


DEAL      SCENARIO      DEAL


are you sure your data is correct? and can you describe what the output is supposed to be using a smaller set?
maybe 5 rows of data and what you expect the output to be for those 5 rows?

I understand this:  while constraints only have 2 levels, each table potentially has a level above it and a level below it.  In other words, the three column list comes from a self join of the constraint table to itself.  

To calculate the level is a recursive operation and I don't think there is any way to do it in Oracle SQL.  You can however, write a recursive procedure that starts from any constraint and climbs up the hierarchy counting parents until no more are found.  However, do note that a table can have more than one constraint and consequently a different level for each one.   In cases where I need to know the level order (such as the correct order to process a series of deletes in a hierarchy (ilel child-up) without violating RI, using the max level of each table has always worked.  


I do agree that you sample query looks suspiciously wrong: the circular references being one example, stipulation_status as a child of stipulation being another.

When there are cyclic relationships (DEAL/SCENARIO or STIPULATION/STIPULATION_STATUS), One of the reference columns is null. In these cases, I can disable the constraints and do the operations and enable them. But, I'm not allowed to do the same for all tables.

This is what I used to populate the above xls.

DECLARE
   V_TBL_NM    VARCHAR2 (50);
   V_TBL_LVL   NUMBER;
BEGIN
   FOR I
      IN (SELECT dt.table_name
            FROM dba_tables dt
           WHERE dt.owner = 'DEAL'
                 AND table_name NOT IN
                        (SELECT table_name FROM grand_parent_tbls
                         UNION
                         SELECT table_name FROM grand_child_tbls)
                 AND TABLE_NAME NOT IN
                        ('PURGE_ASSOCIATION',
                         'QUEST_SL_TEMP_EXPLAIN1',
                         'PLAN_TABLE',
                         'TOAD_PLAN_TABLE',
                         'DEPLOYMENT_PROPERTIES',
                         'DEAL_PURGED',
                         'EMAIL_NOTIFICATION',
                         'RECEIVING_VALIDATION'))
   LOOP
      V_TBL_NM := I.TABLE_NAME;

      -- list the child tables which are not grand childs for the non grand parent/child tables
      FOR J IN (SELECT DISTINCT dc1.table_name, dc2.table_name child_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.r_constraint_name = dc1.constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_child_tbls
                                WHERE table_name = dc2.table_name))
      LOOP
         INSERT INTO new_parent_child_tbls (table_name, child_table)
              VALUES (v_tbl_nm, j.child_table);
      END LOOP;

      COMMIT;

      -- -- list the parent tables which are not grand grand parents for the non grand parent/child tables
      FOR k IN (SELECT dc1.table_name, dc2.table_name parent_table
                  FROM dba_constraints dc1, dba_constraints dc2
                 WHERE     dc1.owner = 'DEAL'
                       AND DC1.TABLE_NAME = v_tbl_nm
                       AND dc1.owner = dc2.owner
                       AND dc2.constraint_name = dc1.r_constraint_name
                       AND NOT EXISTS
                              (SELECT 1
                                 FROM grand_parent_tbls
                                WHERE table_name = dc2.table_name))
      LOOP
         UPDATE new_parent_child_tbls
            SET parent_table = k.parent_table
          WHERE table_name = v_tbl_nm;
      END LOOP;

      COMMIT;
   END LOOP;

   COMMIT;
END;

After trying many ways, I have spent two working days to wrap the hierarchy of this complicated OLTP system manually by using the list of parent child tables i have found from the code i tried with hell of temporary tables. Thanks much for your support. It's great that you guyz jump in as soon as you can into the problem.