Last active
August 25, 2017 08:01
-
-
Save nyg/57b2719feba6cded14b01e53530941ef to your computer and use it in GitHub Desktop.
Oracle: delete a row and all rows preventing it to be deleted because of "child record found" errors.
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 | |
-- ORA-02292: integrity constraint (OWNER.FK) violated - child record found | |
ora_02292 EXCEPTION; | |
PRAGMA EXCEPTION_INIT(ora_02292, -2292); | |
l_foreign_key VARCHAR2(400); | |
PROCEDURE delete_row(pi_table IN VARCHAR2, | |
pi_column IN VARCHAR2, | |
pi_value IN VARCHAR2) IS | |
l_new_table all_cons_columns.table_name%TYPE; | |
l_new_column all_cons_columns.column_name%TYPE; | |
l_new_value VARCHAR2(400); | |
l_referenced_column all_cons_columns.column_name%TYPE; | |
BEGIN | |
dbms_output.put_line('DELETE FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' || pi_value); | |
EXECUTE IMMEDIATE 'DELETE FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' || pi_value; | |
EXCEPTION | |
WHEN ora_02292 THEN | |
IF REGEXP_LIKE(SQLERRM, '^ORA-02292: integrity constraint (.+) violated - child record found$') | |
THEN | |
-- retrieve foreign key name | |
l_foreign_key := regexp_replace(SQLERRM, '.*\((.+)\).*', '\1'); | |
-- retrieve table & column name from which we must remove the child record | |
SELECT table_name, column_name | |
INTO l_new_table, l_new_column | |
FROM all_cons_columns | |
WHERE owner || '.' || constraint_name = l_foreign_key; | |
-- retrieve column of pi_table which is referenced by the foreign key | |
SELECT c.column_name | |
INTO l_referenced_column | |
FROM all_cons_columns c, all_constraints a | |
WHERE c.constraint_name = a.r_constraint_name | |
AND a.owner || '.' || a.constraint_name = l_foreign_key; | |
EXECUTE IMMEDIATE 'SELECT ' || l_referenced_column || ' FROM ' || pi_table || ' WHERE ' || pi_column || ' = ' || | |
pi_value | |
INTO l_new_value; | |
dbms_output.put_line(' Found foreign key: ' || l_foreign_key); | |
-- recursively delete children | |
delete_row(l_new_table, l_new_column, l_new_value); | |
-- delete current row | |
delete_row(pi_table, pi_column, pi_value); | |
END IF; | |
END delete_row; | |
BEGIN | |
delete_row('table_name', 'column_name', 'value'); | |
END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment