set serveroutput on

declare
  v_colname VARCHAR2(30);
  v_sql VARCHAR2(2000);
  v_count number;
  v_valore VARCHAR2(255) := 'TRENTO';
  
  cursor curtb is
    select table_name from user_tables;
    
  cursor curcol (p_tbname VARCHAR2) is
    select column_name from user_tab_cols where data_type='VARCHAR2' and table_name=p_tbname order by column_id;    
begin

  select count(*) into v_count from user_tables where table_name = 'TB_CERCAVALORE';
  if v_count = 0 then
    v_sql := 'CREATE TABLE TB_CERCAVALORE (tbname varchar2(30), colname varchar2(30), cnt number)';
    execute immediate v_sql;  
  else
    v_sql := 'DELETE FROM TB_CERCAVALORE';
    execute immediate v_sql;  
  end if;
  
  for rtb in curtb loop
    
      OPEN curcol(rtb.table_name);
      LOOP
        FETCH curcol INTO v_colname;
        EXIT WHEN curcol%NOTFOUND;
        
        v_sql := 'CREATE OR REPLACE VIEW VIEW_CERCAVALORE (CERCA) AS SELECT ' || v_colname || ' FROM ' || rtb.table_name;
        execute immediate v_sql;
        
        insert into TB_CERCAVALORE (tbname, colname, cnt)
        select rtb.table_name, v_colname, count(*) from VIEW_CERCAVALORE where cerca like '%' || v_valore || '%';
        
      END LOOP;
      close curcol;
  end loop;
end;
/

select * from TB_CERCAVALORE;