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

Oracle Connect By Clause to Perform Hierarchical Queries.

If we want to perform hierarchical queries we need to use connect by clause in our sql query. Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure. One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table. But we are not going to take it as you can reference it almost on any site that hosts oracle based content. We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former. In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent. with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME This co...

QueryLight

Search This Blog

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

Labels

Comments

Post a Comment

Popular posts from this blog

Use the Microsoft Visual Studio Code(VScode) with Java Maven project.

Oracle Connect By Clause to Perform Hierarchical Queries.

Introduction to Oracle Analytical functions , rank , denserank.