How to delete all the records containing a given value on a given named column from all the tables continaing that named column, in the schema.

Let's say, we have to delete all the records  containing a given value on a given named column from all the tables continaing that named column, in the schema. In my example, the column is THE_NAME_OF_THE_COL and the value is 40322.

This can be a way to do it:

first, create the tables:

/*all the tables containing the wanted column:*/
create table tabele_codmis as (select table_name from user_tab_columns a where (a.column_name='THE_NAME_OF_THE COL' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0 );
/*all the table containing the wanted column and having at least one record containing a given value on that column (will be emptied and filled within the procedure: */
create table tabele_codmis_ex  as select * from tabele_codmis;

DECLARE

cursor r1 is select * from tabele_codmis;
cursor r2 is select * from tabele_codmis_ex;
cursor r4 is select * from constr_mis_ex;
cate number;
miscod number;
sql2 varchar2(1000);
numet varchar2(1000);

BEGIN


/* empty the table continaing the names of tables containing the given column*/
execute immediate 'delete from tabele_codmis';


/* fill the above table*/
execute immediate 'insert into tabele_codmis select table_name from user_tab_columns a where (a.column_name=''THE_NAME_OF_THE_COL'' ) and (select count(*) from user_tables b where a.table_name=b.table_name)>0';


/* empty the table containing the given value*/
execute immediate 'delete from tabele_codmis_ex';


/* fill the table above */
FOR c1 IN r1

  loop

      cate:=0;

      miscod:=40322; -- this was my value

      execute immediate 'select count(*)   from '||c1.table_name||' where THE_NAME_OF_THE_COL =:1' into cate using miscod;

      if cate>0 then

        sql2:='insert into tabele_codmis_ex values (:1)';


         select c1.table_name into numet from dual;
        execute immediate sql2 using numet;
      end if;  END LOOP;

/* drop the table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('drop table constr_mis_ex');
/* create and fill table containing the enabled constraints contained by the wanted tables*/
dbms_utility.exec_ddl_statement('create table constr_mis_ex as select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, R_OWNER, R_CONSTRAINT_NAME, DELETE_RULE, STATUS, DEFERRABLE, DEFERRED, VALIDATED, GENERATED, BAD, RELY, LAST_CHANGE, INDEX_OWNER, INDEX_NAME, INVALID, VIEW_RELATED from user_constraints  where constraint_type=''R'' and status=''ENABLED'' and table_name in (select table_name from tabele_codmis_ex)'); 

/* we are interested only in referential constraints*/

/* disable all the constraints from above */

for c4 in r4

    loop

    dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' read only'); /* this is not mandatory, but you could find it useful, otherwise you can experience some locking erros */
    dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' disable novalidate constraint ' || c4.constraint_name);
end loop;

 

/* delete all the records containing the given value */

FOR c2 IN r2
  loop      execute immediate 'delete from  '||c2.table_name||' where THE_NAME_OF_THE_COL =:1'  using miscod;
END LOOP;


/* enable back the constraints back */
for c4 in r4
    loop
       dbms_utility.exec_ddl_statement('alter table '||c4.table_name || ' enable novalidate constraint ' || c4.constraint_name);     dbms_utility.exec_ddl_statement('alter table  '||c4.table_name || '  read write');
    end loop;
end;
/


 Now all the unwanted records should have been erased. Enjoy!