create or replace FUNCTION VALUE_PRESENT
    (table_name IN VARCHAR2
    ,column_name IN VARCHAR2
    ,TEXT IN VARCHAR2
    )
RETURN NUMBER
AS
    nVAL NUMBER(17);
BEGIN
    BEGIN
    EXECUTE IMMEDIATE
        'select count(*)'||
        ' from '||table_name||
        ' where '||column_name||' like ''%'||TEXT||'%'''
        INTO nval;
    EXCEPTION WHEN OTHERS THEN
        RETURN 0;
    END;
    RETURN nVAL;
END;

SELECT table_name
     , column_name
FROM COLS
WHERE COLS.data_type LIKE '%CHAR%'
  AND VALUE_PRESENT(table_name, column_name, 'Иванов') > 0;

AND VALUE_PRESENT(table_name, column_name, 'Иванов') = 1;

set serveroutput on
declare
  l_pattern varchar2(100) := '%КР-Ф/34/08%';

  cursor cf is
    select table_name,column_name 
      from user_tab_columns
     where data_type = 'VARCHAR2'
     order by table_name;

  t_stmnt varchar2(2000) := 'select count(*) from dual where exists( select null from ';
  l_stmnt varchar2(2000); 
  l_where_clause varchar2(2000) := ' where 1=0';

  l_last_table varchar2(100) := '';
  l_cnt number := 0;
  
  procedure print_query_text (i_text varchar2) is
    l_text varchar2(4000) := i_text;
    l_len number := length(l_text);
    l_pieces number := ceil(l_len/250);
  begin
    for i in 1..l_pieces loop
      dbms_output.put_line(substr(i_text, 1+250*(i-1),least(250,l_len-250*(i-1))));
    end loop;
    dbms_output.new_line;
  end;

begin
  for i in cf loop
    if l_last_table <> i.table_name then
    --  dbms_output.put_line(l_stmnt||l_where_clause);
      execute immediate l_stmnt || l_where_clause ||')' into l_cnt;
      if l_cnt > 0 then 
        print_query_text ('select * from ' || l_last_table || l_where_clause || ';');
      end if;
      l_cnt := 0;
      l_where_clause := ' where 1=0';
    end if;
    
    l_last_table := i.table_name;
    
    l_stmnt := t_stmnt || i.table_name;
    
    l_where_clause := l_where_clause || ' OR ' || i.column_name || ' like ''' ||l_pattern||'''';
   
  end loop;
  execute immediate l_stmnt || l_where_clause ||')' into l_cnt;
  if l_cnt > 0 then 
    print_query_text ('select * from ' || l_last_table || l_where_clause || ';');
  end if;
end;