Monday 17 September 2012

Diagnose ORA-02292 Errors When Deleting Records in Oracle

Working on an undocumented system and got the error “ORA-02292: integrity constraint (…) violated - child record found” when trying to delete records? This solution provides an anonymous procedure for running in SQL*Plus, Developer, etc, to get the child relationships for a specific table, so you can easily understand the constraints preventing the record being deleted.

The procedure needs two parameters, the start table and the rowid of the record. The following code can be prefixed to the procedure in the script to set up these variables.

VAR vRowId VARCHAR2(20) VAR vSchema VARCHAR2(30) VAR vStartTable VARCHAR2(30) BEGIN :vSchema := USER; :vStartTable := 'MY_TABLE'; SELECT ROWID INTO :vRowId FROM my_table WHERE my_table_id = 5333; END; /
Adjust the query or just add the rowid reference directly, as required. The procedure then uses these values to produce the output, which will be specifically for the record with the rowid selected. Relationships with no data will not be displayed, so you can track down the cause of the ORA-02292 error.

DECLARE --//------------------------------ c_schema CONSTANT VARCHAR2(30) := :vSchema; c_startTable CONSTANT VARCHAR2(30) := :vStartTable; c_startRowId CONSTANT VARCHAR2(20) := :vRowId; --//------------------------------ TYPE t_consColumns IS TABLE OF user_cons_columns.column_name%TYPE; TYPE t_results IS TABLE OF ROWID; TYPE t_processed IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(50); g_processed t_processed; --//------------------------------ FUNCTION getChildRowids ( p_parentTable IN VARCHAR2, p_childTable IN VARCHAR2, p_parentColumns IN t_consColumns, p_childColumns IN t_consColumns, p_rowid IN ROWID ) RETURN t_results IS l_query VARCHAR2(4000); l_results t_results; BEGIN l_query := 'SELECT c.ROWID' || ' FROM '||c_schema||'.'||p_parentTable||' p,' || c_schema||'.'||p_childTable ||' c' ||' WHERE p.ROWID = '''||p_rowid||''''; FOR i IN 1..p_parentColumns.COUNT LOOP l_query := l_query||' AND p.'||p_parentColumns(i)||' = c.'||p_childColumns(i); END LOOP; EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_results; RETURN(l_results); END getChildRowids; --//------------------------------ FUNCTION getConstraintColumns ( p_constraintName IN VARCHAR2 ) RETURN t_consColumns IS l_columnNames t_consColumns; BEGIN SELECT column_name BULK COLLECT INTO l_columnNames FROM dba_cons_columns WHERE owner = c_schema AND constraint_name = p_constraintName; RETURN(l_columnNames); END getConstraintColumns; --//------------------------------ FUNCTION alreadyProcessed ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) RETURN BOOLEAN IS l_return BOOLEAN := FALSE; BEGIN IF g_processed(p_tableName||'.'||p_rowid)=1 THEN NULL; END IF; RETURN(TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN g_processed(p_tableName||'.'||p_rowid) := 1; RETURN(FALSE); END; --//------------------------------ PROCEDURE processTable ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) IS l_parentColumns t_consColumns; l_childColumns t_consColumns; l_results t_results; BEGIN IF NOT alreadyProcessed(p_tableName,p_rowid) THEN FOR c IN ( SELECT parent.constraint_name parentConstraint, child.constraint_name childConstraint, child.table_name childTable FROM dba_constraints parent, dba_constraints child WHERE parent.table_name = p_tableName AND parent.constraint_name = child.r_constraint_name AND child.r_owner = c_schema AND parent.owner = c_schema ) LOOP l_parentColumns := getConstraintColumns(c.parentConstraint); l_childColumns := getConstraintColumns(c.childConstraint); l_results := getChildRowids(p_tableName, c.childTable, l_parentColumns, l_childColumns, p_rowid); FOR r IN 1..l_results.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Found child records'); DBMS_OUTPUT.PUT_LINE('- for: '||p_tableName||' ['||p_rowid||']'); DBMS_OUTPUT.PUT_LINE('- at: '||c.childTable||' ['||l_results(r)||']'); DBMS_OUTPUT.PUT_LINE('- via: '||c.childConstraint||' -> '||c.parentConstraint); processTable(c.childTable, l_results(r)); END LOOP; END LOOP; END IF; END processTable; --//------------------------------ BEGIN DBMS_OUTPUT.PUT_LINE('Processing '||c_startTable||' ['||c_startRowId||']'); processTable(c_startTable, c_startRowId); END; /

The main sources of data are the views dba_constraints and dba_cons_columns. The procedure uses these to build a dynamic query to check for related data in the child tables. If data is found it outputs the result, then checks down that branch for further child tables.

The procedure only checks one schema, so relationships between schemas will not be included in the search.

Child Records Found

No comments: