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)
- l_table_condition
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;
/
