Oracle Procedures to delete the parent child table rows with integrity constraints.

With some very hard work and innovative thinking we came up with the procedures (can be easily clubbed into package) to delete the parent child relationship row with any number of levels.

Below code would find the child row of the parent row being deleted and delete it first , if the child row has its own child then that childs children are fetched and deleted so its basically a recursive algorithm

There are not many situations where you would want to use this. We had a customer mstr record to be deleted which had 20 levels of child records held tight by (ORA-02292: integrity constraint   violated - child record found ) with their parent child constraints.

Since this only was a delete for 1 Master record we went ahead and used this script. If you have more than 20 levels Wise course would be to disable constraints and really think why you want to delete such master record.

Note of caution : BE CAREFUL using this on production environment.

The procedures create the insert statements along the line as it deletes the parent child rows  , but  its done in DBMS_OUTPUT so make sure you have that turned on if you want insert statement roll back plan to be created.  You can of course be innovative and dump inserts in another table or use utl_file to write them elsewhere.

Usage : Find DEL_PARENT_CHILD On
2nd Last line is the call to main recursive delete.
1st Argument is TABLE NAME here (Order_Header_TBL)
2nd Argument is Where Clause  PROCEDURE CAN DELETE ONLY 1 PARENT LINE AT A TIME.
MAKE SURE YOUR WHERE CLAUSE IS FETCHING ONLY 1 ROW.
If you want to delete multiple rows call it that many times in a lopp or whichever way you see fit.

set LINES 3000 set serverout on alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; DECLARE TYPE COL_LIST_TYPE IS VARRAY(255) OF VARCHAR2(3000); PROCEDURE CREATE_INSERT_STMT(V_TABLE_NAME VARCHAR2 , V_WHERE_CLAUSE VARCHAR2 ) IS TYPE COL_LIST_TYPE IS VARRAY(255) OF VARCHAR2(4000); TYPE COL_LIST_DATA_TYPE IS VARRAY(255) OF VARCHAR2(20); COL_LIST COL_LIST_TYPE := COL_LIST_TYPE(); COL_LIST_dt_typ COL_LIST_DATA_TYPE := COL_LIST_DATA_TYPE(); columns_clause varchar2(4000); VALUES_CLAUSE VARCHAR2(4000):= ' '; filler varchar2(50); IGNORE NUMBER; column_count integer; sql_cursor integer; cursor TBL_COLS is select column_name, data_length , column_id ,data_type from all_Tab_columns where table_name = v_table_name order by column_id; BEGIN execute immediate 'alter session set nls_date_format = ''DD-MON-YYYY HH24:MI:SS'''; COL_LIST.EXTEND(250); COL_LIST_dt_typ.EXTEND(250); SQL_CURSOR:=DBMS_SQL.OPEN_CURSOR; --dbms_output.put_line( 'FROM INS : select * from ' || v_table_name || ' ' || v_where_clause); DBMS_SQL.Parse(sql_cursor, 'select * from ' || v_table_name || ' ' || v_where_clause , 1); for tbl_col in tbl_cols LOOP SELECT DECODE(COLUMNS_CLAUSE, NULL , ' ' , ',') INTO FILLER FROM DUAL; COLUMNS_CLAUSE := COLUMNS_CLAUSE || FILLER || TBL_COL.COLUMN_NAME; COL_LIST_dt_typ(TBL_COL.COLUMN_ID):= tbl_col.data_type; if tbl_col.data_type in ('VARCHAR2' , 'NUMBER', 'CHAR') Then DBMS_SQL.DEFINE_COLUMN(SQL_CURSOR, TBL_COL.COLUMN_ID, COL_LIST(TBL_COL.COLUMN_ID), TBL_COL.DATA_LENGTH); ELSE DBMS_SQL.DEFINE_COLUMN(SQL_CURSOR, TBL_COL.COLUMN_ID, COL_LIST(TBL_COL.COLUMN_ID), 2000); end if; end LOOP; COLUMNS_CLAUSE:='insert into ' || v_table_name || ' (' || COLUMNS_CLAUSE || ')' ; IGNORE := DBMS_SQL.EXECUTE (SQL_CURSOR); select max(column_id) into column_count from all_Tab_columns where table_name = v_table_name; WHILE DBMS_SQL.FETCH_ROWS(SQL_CURSOR) > 0 LOOP VALUES_CLAUSE:= ' '; for i in 1..column_count loop DBMS_SQL.COLUMN_VALUE(SQL_CURSOR, I, COL_LIST(I)); COL_LIST(I):=replace(COL_LIST(I),'''','`'); select decode(COL_LIST_DT_TYP(I) , 'DATE' , 'to_date(''' || col_list(i) || ''',''DD-MON-YYYY HH24:MI:SS'')', '''' || col_list(i) || '''') into col_list(i) from dual ; SELECT DECODE (I , COLUMN_COUNT , null , ',') INTO FILLER FROM DUAL; values_clause := values_clause || col_list(i) || filler; end LOOP; VALUES_CLAUSE := ' values ( ' || VALUES_CLAUSE || ' ) '; dbms_output.put_line ( COLUMNS_CLAUSE || chr(10)|| VALUES_CLAUSE || ';'); END LOOP; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('sorry couldnT SELECT FROM :' ); DBMS_OUTPUT.PUT_LINE('select * from ' || v_table_name || ' ' || v_where_clause); end CREATE_INSERT_STMT; procedure del_parent_child ( p_tab_name varchar2 ,p_where_clause varchar2) is v_child_select varchar2(3000); v_parent_select varchar2(3000); c_ref_cursor SYS_REFCURSOR; v_cnt number; v_tmp_col varchar2(500); v_where_clause varchar2(3000); v_where_str varchar2(400); V_PARENT_WHERE VARCHAR2(3000); v_col_count number:=0; cursor c_child_tabs(p_parent varchar2) is select distinct con.table_name child_tab --CHILD RECORD for table from all_constraints con , all_cons_columns con_col , all_cons_columns child_col where con_col.table_name =p_parent and con.CONSTRAINT_TYPE = 'R' and con.R_owner=con_col.owner and con.r_CONSTRAINT_NAME = con_col.CONSTRAINT_NAME and CHILD_COL.CONSTRAINT_NAME = con.CONSTRAINT_NAME and CHILD_COL.owner = con.owner and child_col.position = con_col.position; cursor c_child_tab_cols( P_child varchar2 ,p_parent varchar2) is select con_col.position recid, con.table_name child_tab , CHILD_COL.column_name child_col ,CON_COL.TABLE_NAME parent_Tab ,con_col.column_name parent_col --CHILD RECORD for table from all_constraints con , all_cons_columns con_col , all_cons_columns child_col where con.table_name =P_child and CON_COL.TABLE_NAME =p_parent and con.CONSTRAINT_TYPE = 'R' and con.R_owner=con_col.owner and con.r_CONSTRAINT_NAME = con_col.CONSTRAINT_NAME and CHILD_COL.CONSTRAINT_NAME = con.CONSTRAINT_NAME and CHILD_COL.owner = con.owner and child_col.position = con_col.position order by con_col.position; begin create_insert_stmt(p_tab_name , p_where_clause); v_child_select:='SELECT '; for c_child_rec in c_child_tabs(p_tab_name) loop v_parent_select:='SELECT '; V_WHERE_CLAUSE:= ' '; V_PARENT_WHERE:= ' where ('; v_col_count:=0; for c_child_tab_cols_rec in c_child_tab_cols ( c_child_rec.child_tab , p_tab_name) LOOP v_where_clause := v_where_clause || 'chr(39)||' || c_child_tab_cols_rec.parent_Col ||'||CHR(39)||' || ''',''||' ; V_PARENT_SELECT := V_PARENT_SELECT || C_CHILD_TAB_COLS_REC.CHILD_COL || ','; V_PARENT_WHERE:=V_PARENT_WHERE || C_CHILD_TAB_COLS_REC.CHILD_COL || ','; v_col_count:=v_col_count+1; --dbms_output.put_line('select count(*) from '|| c_child_rec.child_tab || ' where ' || c_child_rec.child_col || '=''' || p_val || ''''); end loop; V_PARENT_WHERE:= SUBSTR(V_PARENT_WHERE,1,LENGTH(V_PARENT_WHERE)-1); V_PARENT_WHERE:= V_PARENT_WHERE || ')'; V_WHERE_CLAUSE:= SUBSTR(V_WHERE_CLAUSE,1,LENGTH(V_WHERE_CLAUSE)-7); v_child_select:= 'select ' || v_where_clause || ' from ' || p_tab_name || p_where_clause; BEGIN V_WHERE_STR:=null; OPEN C_REF_CURSOR FOR V_CHILD_SELECT; loop FETCH C_REF_CURSOR INTO V_WHERE_STR; exit when C_REF_CURSOR%notfound; IF V_WHERE_STR IS NULL THEN GOTO CARRY_ON; /* FOR I IN 1..V_COL_COUNT LOOP V_WHERE_STR:=V_WHERE_STR||'null,'; END LOOP; V_WHERE_STR:=SUBSTR(V_WHERE_STR,1,LENGTH(V_WHERE_STR)-1); */ end if; V_WHERE_STR := '((' || V_WHERE_STR || '))'; --DBMS_OUTPUT.PUT_LINE('key : ' || V_WHERE_STR); DEL_PARENT_CHILD ( C_CHILD_REC.CHILD_TAB,V_PARENT_WHERE || 'in' || V_WHERE_STR ); end loop; close c_ref_cursor; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('EXCem REF CUR' || v_child_select || sqlerrm); END; --del_parent_child ( c_child_rec.child_tab,v_parent_where || 'in' || v_where_str ); --dbms_output.put_line(v_parent_select); <<carry_on>> null; end loop; begin EXECUTE IMMEDIATE ('delete from '|| P_TAB_NAME || P_WHERE_CLAUSE ); IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE( 'Count deleted: ' || SQL%ROWCOUNT ||' delete from '|| P_TAB_NAME || P_WHERE_CLAUSE ); end if; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( 'ERROR : '|| sqlerrm || sql%rowcount ||'delete from '|| p_tab_name || p_where_clause ); end; end; BEGIN DEL_PARENT_CHILD ( 'ORDER_HEADER_TBL' , ' where rowid =''AABqpXAALAABgFFAAR''' ); END; /