Generate queries for PK-FK data check

Given:

• table to be inspected
  • l_owner
  • l_table_name
• row in this table
  • l_table_condition
    Result: generated queries to extract parent rows (Reference to section) and child rows (Referenced by section)

DECLARE
   l_owner VARCHAR2(30) := UPPER('schema_name');
   l_table_name VARCHAR2(30) := UPPER('table_name');
   l_table_condition VARCHAR2(32767) := 't.id = 133';
   
   l_chi_cols VARCHAR2(32767);
   l_par_cols VARCHAR2(32767);
         
   FUNCTION get_constraint_columns(p_cons_owner VARCHAR2, p_cons_name VARCHAR2) RETURN VARCHAR2 IS
      l_result VARCHAR2(32767);
   BEGIN
      SELECT LISTAGG(cols.column_name, ', ') WITHIN GROUP (ORDER BY cols.position)
      INTO l_result 
      FROM dba_cons_columns cols
      WHERE cols.owner = p_cons_owner
        AND cols.constraint_name = p_cons_name;
      
      RETURN l_result;   
   END;
BEGIN
   dbms_output.put_line('-- Reference to');
   dbms_output.put_line(LPAD('-', 10, '-'));  
   FOR rec IN (
      SELECT c.owner chi_owner, c.table_name chi_table_name, c.constraint_name chi_constraint_name
        , p.owner par_owner, p.table_name par_table_name, p.constraint_name par_constraint_name
      FROM dba_constraints c,
        dba_constraints p
      WHERE c.owner = l_owner
        AND c.table_name = l_table_name
        AND c.constraint_type = 'R'
        AND p.owner = c.r_owner
        AND p.constraint_name = c.r_constraint_name
   ) LOOP
      l_chi_cols := get_constraint_columns(rec.chi_owner, rec.chi_constraint_name);
      l_par_cols := get_constraint_columns(rec.par_owner, rec.par_constraint_name);
      dbms_output.new_line; 
      dbms_output.put_line('-- ' || rec.chi_owner || '.' || rec.chi_table_name || '.' || l_chi_cols || ' -> ' 
         || rec.par_owner || '.' || rec.par_table_name || '.' || l_par_cols);
      dbms_output.put_line(
'SELECT * ' || CHR(10) ||
'FROM ' || rec.par_owner || '.' || rec.par_table_name || ' p' || CHR(10) ||
'WHERE (' || l_par_cols || ') IN (' || CHR(10) ||     
'  SELECT ' || l_chi_cols || CHR(10) ||
'  FROM ' || rec.chi_owner || '.' || rec.chi_table_name || ' t' || CHR(10) ||
'  WHERE ' || l_table_condition || CHR(10) ||
');'    
); 
   END LOOP;
   
   dbms_output.put_line('-- Referenced by');
   dbms_output.put_line(LPAD('-', 10, '-'));  
   FOR rec IN (
      SELECT c.owner chi_owner, c.table_name chi_table_name, c.constraint_name chi_constraint_name
        , p.owner par_owner, p.table_name par_table_name, p.constraint_name par_constraint_name
      FROM dba_constraints c,
        dba_constraints p
      WHERE p.owner = l_owner
        AND p.table_name = l_table_name
        AND p.constraint_type = 'P'
        AND p.owner = c.r_owner
        AND p.constraint_name = c.r_constraint_name
   ) LOOP
      l_chi_cols := get_constraint_columns(rec.chi_owner, rec.chi_constraint_name);
      l_par_cols := get_constraint_columns(rec.par_owner, rec.par_constraint_name);
      dbms_output.new_line; 
      dbms_output.put_line('-- ' || rec.chi_owner || '.' || rec.chi_table_name || '.' || l_chi_cols || ' -> ' 
         || rec.par_owner || '.' || rec.par_table_name || '.' || l_par_cols);
      dbms_output.put_line(
'SELECT * ' || CHR(10) ||
'FROM ' || rec.chi_owner || '.' || rec.chi_table_name || ' c' || CHR(10) ||
'WHERE (' || l_chi_cols || ') IN (' || CHR(10) ||     
'  SELECT ' || l_par_cols || CHR(10) ||
'  FROM ' || rec.par_owner || '.' || rec.par_table_name || ' t' || CHR(10) ||
'  WHERE ' || l_table_condition || CHR(10) ||
');'    
); 
   END LOOP;   
END;
/