Created
January 11, 2017 10:03
-
-
Save nyg/eb091e6c7c59b71fa4c9d49860574d2c to your computer and use it in GitHub Desktop.
Oracle: outputs the necessary delete statements to delete a row and all rows preventing it from being deleted because of "child record found" error.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE | |
g_level INTEGER := 0; | |
g_owner VARCHAR2(10) := 'owner'; | |
PROCEDURE find_fk(pi_table IN VARCHAR2, | |
pi_where IN VARCHAR2) IS | |
l_where VARCHAR2(500); | |
l_pad VARCHAR2(100); | |
BEGIN | |
g_level := g_level + 1; | |
FOR i IN (SELECT acc_a.table_name, acc_a.column_name, acc_b.table_name r_table_name, acc_b.column_name r_column_name | |
FROM all_cons_columns acc_a, all_constraints ac, all_cons_columns acc_b | |
WHERE acc_a.owner = g_owner | |
AND acc_a.constraint_name = ac.constraint_name | |
AND ac.owner = g_owner | |
AND ac.constraint_type = 'R' | |
AND ac.r_constraint_name = acc_b.constraint_name | |
AND acc_b.owner = g_owner | |
AND acc_b.table_name = pi_table) | |
LOOP | |
l_pad := lpad(' ', 2 * (g_level - 1)); | |
l_where := 'select ' || i.r_column_name || ' from ' || i.r_table_name || pi_where; | |
IF i.table_name <> pi_table | |
THEN | |
find_fk(i.table_name, ' where ' || i.column_name || ' = (' || l_where || ')'); | |
END IF; | |
dbms_output.put_line(l_pad || 'delete from ' || i.table_name || ' where ' || i.column_name || ' = (' || l_where || ');'); | |
END LOOP; | |
g_level := g_level - 1; | |
END; | |
BEGIN | |
find_fk('table_name', ' where column_name = value'); | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment