Oracle - validate table exists with same name with column with table name value

Question

We have table which have column TABLE_NAME which hold real tables names in same scheme

We use tables names values to create dynamic SQL inserts

Can those names be validated to hold a valid table name?

For example if someone rename table which is exists in column TABLE_NAME , to alert (similar to constraint/key) with ora exception or other way?

Answer 1

I think you might use a DDL trigger to raise an error or use dbms_output to get an alert

Something like this ( Update to your own scenario )

SQL> create table  my_table_list ( c1 varchar2(1) ) ;

Table created.

SQL> insert into my_table_list values ( 'T' ) ;

1 row created.

SQL>  create table t ( c1 number ) ;

Table created.

Then we use a DDL trigger on schema

create or replace trigger audit_ddl_trg after rename on schema
declare
vcounter pls_integer;
begin
  if (ora_sysevent='RENAME')
  then
      select count(*) into vcounter from cpl_rep.my_table_list where c1 = upper(ora_dict_obj_name);
      if vcounter > 0 
      then 
        raise_application_error(-20001, 'Rename not allowed. Table does not exist');
      end if;
 end if;
end;
/

SQL> rename x to t;

Table renamed.

SQL> rename t to x ;
rename t to x
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'CPL_REP.AUDIT_DDL_TRG'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Rename not allowed. Table does not exist
ORA-06512: at line 9

It could be improved to control more things, but I think this covers your question.