DECLARE
/**
* Please change this one to any client id you want to delete
**/
v_Client_ID NUMBER := 1000003;
v_SQL1 VARCHAR2(1024);
CURSOR Cur_Contraints IS
select table_name,constraint_name
from user_constraints
where constraint_type = 'R'
AND status='ENABLED';
CURSOR Cur_Triggers IS
select TRIGGER_NAME
from user_triggers
where status='ENABLED';
CURSOR Cur_RemoveData IS
select 'delete from '|| TABLENAME ||' where AD_Client_ID=' || v_Client_ID
AS v_SQL
from AD_Table a
where a.ISVIEW='N'
AND exists ( select AD_Column_ID from AD_Column c where a.AD_Table_ID=c.AD_Table_ID
and upper(c.COLUMNNAME)= upper('AD_Client_ID') );
BEGIN
DBMS_OUTPUT.PUT_LINE(' Delete Client Where AD_Client_ID=' || v_Client_ID);
/****************************************************************
* Disable all the constraints one by one
****************************************************************/
DBMS_OUTPUT.PUT_LINE(' Disable the contraints ');
FOR p IN Cur_Contraints LOOP
v_SQL1 := 'alter table '|| p.table_name ||' disable constraint '|| p.constraint_name;
EXECUTE IMMEDIATE v_SQL1;
END LOOP; -- Disable contraints
DBMS_OUTPUT.PUT_LINE(' Disable the triggers ');
FOR p IN Cur_Triggers LOOP
v_SQL1 := 'alter trigger '|| p.TRIGGER_NAME ||' disable ';
EXECUTE IMMEDIATE v_SQL1;
END LOOP; -- Disable contraints
/****************************************************************
* Remove all the records belongs to that client
****************************************************************/
FOR p IN Cur_RemoveData LOOP
v_SQL1 := p.v_SQL;
EXECUTE IMMEDIATE v_SQL1;
END LOOP; -- Remove data
/****************************************************************
* Disable all the constraints one by one
****************************************************************/
DBMS_OUTPUT.PUT_LINE(' Enable the contraints ');
FOR p IN Cur_Contraints LOOP
v_SQL1 := 'alter table '|| p.table_name ||' enable constraint '|| p.constraint_name;
EXECUTE IMMEDIATE v_SQL1;
END LOOP; -- Enable contraints
DBMS_OUTPUT.PUT_LINE(' Enable the triggers ');
FOR p IN Cur_Triggers LOOP
v_SQL1 := 'alter trigger '|| p.TRIGGER_NAME ||' enabled ';
EXECUTE IMMEDIATE v_SQL1;
END LOOP; -- Enable contraints
COMMIT;
END;