Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t42(str) as select 'xxx@pqr.de' from dual;
1 rows affected

create table t43(str) as select 'xxx@pqr.de' from dual;
1 rows affected

DECLARE 
    l_sql    CLOB; 
    l_where  CLOB; 
    l_result INT; 
BEGIN 
    FOR i IN (SELECT table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
              FROM   all_tab_columns 
              WHERE  owner in (USER) -- list relevant schemas
              AND    data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
        IF i.seq = 1 THEN 
          l_sql := 'select ' 
                   ||Chr(10); 

          l_where := 'where ' 
                     ||Chr(10); 
        END IF; 

        l_sql := l_sql 
                 || '  max(case when ' 
                 ||i.column_name 
                 ||' like ''%@pqr.de%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
                   || '  ' 
                   ||i.column_name 
                   ||' is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
                   ||'from ' 
                   ||i.table_name 
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 

          dbms_output.Put_line('---------------------------------------'); 

          dbms_output.Put_line(l_sql); 

          EXECUTE IMMEDIATE l_sql INTO l_result; 

          IF l_result > 0 THEN 
            dbms_output.Put_line('Found!!! l_result=' 
                                 ||l_result); 
          END IF; 
        END IF; 
    END LOOP; 
END; 

/ 
1 rows affected

dbms_output:
---------------------------------------
select 
  max(case when STR like '%@pqr.de%' then 1 else 0 end)
from T42
where 
  STR is not null
Found!!! l_result=1
---------------------------------------
select 
  max(case when STR like '%@pqr.de%' then 1 else 0 end)
from T43
where 
  STR is not null
Found!!! l_result=1

create table "TABLE" ("COLUMN") as select 'X' from dual;
1 rows affected

DECLARE 
    l_sql    CLOB; 
    l_where  CLOB; 
    l_result INT; 
BEGIN 
    FOR i IN (SELECT table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
              FROM   all_tab_columns 
              WHERE  owner in (USER) -- list relevant schemas
              AND    data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
        IF i.seq = 1 THEN 
          l_sql := 'select ' 
                   ||Chr(10); 

          l_where := 'where ' 
                     ||Chr(10); 
        END IF; 

        l_sql := l_sql 
                 || '  max(case when ' 
                 ||i.column_name 
                 ||' like ''%@pqr.de%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
                   || '  ' 
                   ||i.column_name 
                   ||' is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
                   ||'from ' 
                   ||i.table_name 
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 

          dbms_output.Put_line('---------------------------------------'); 

          dbms_output.Put_line(l_sql); 

          EXECUTE IMMEDIATE l_sql INTO l_result; 

          IF l_result > 0 THEN 
            dbms_output.Put_line('Found!!! l_result=' 
                                 ||l_result); 
          END IF; 
        END IF; 
    END LOOP; 
END; 

/ 


dbms_output:
---------------------------------------
select 
  max(case when STR like '%@pqr.de%' then 1 else 0 end)
from T42
where 
  STR is not null
Found!!! l_result=1
---------------------------------------
select 
  max(case when STR like '%@pqr.de%' then 1 else 0 end)
from T43
where 
  STR is not null
Found!!! l_result=1
---------------------------------------
select 
  max(case when COLUMN like '%@pqr.de%' then 1 else 0 end)
from TABLE
where 
  COLUMN is not null

ORA-00936: missing expression
ORA-06512: at line 55

DECLARE 
    l_sql    CLOB; 
    l_where  CLOB; 
    l_result INT; 
BEGIN 
    FOR i IN (SELECT owner,
                     table_name, 
                     column_name, 
                     Row_number() 
                       over ( 
                         PARTITION BY table_name 
                         ORDER BY column_name )    AS seq, 
                     Count(*) 
                       over ( 
                         PARTITION BY table_name ) AS cnt 
              FROM   all_tab_columns 
              WHERE  owner in (USER) -- list relevant schemas
              AND    data_type IN ( 'CHAR', 'CLOB', 'NCHAR', 'NVARCHAR2', 
                                    'VARCHAR2' 
                                  )) LOOP 
        IF i.seq = 1 THEN 
          l_sql := 'select ' 
                   ||Chr(10); 

          l_where := 'where ' 
                     ||Chr(10); 
        END IF; 

        l_sql := l_sql 
                 || '  max(case when "' 
                 ||i.column_name 
                 ||'" like ''%@pqr.de%'' then ' 
                 ||Power(2, i.seq - 1) 
                 ||' else 0 end)+' 
                 ||Chr(10); 

        l_where := l_where 
                   || ' "' 
                   ||i.column_name 
                   ||'" is not null or' 
                   ||Chr(10); 

        IF i.seq = i.cnt THEN 
          l_sql := Rtrim(l_sql, '+' 
                                ||Chr(10)) 
                   ||Chr(10) 
                   ||'from "'
                   ||i.owner
                   ||'"."'
                   ||i.table_name
                   ||'"'
                   ||Chr(10) 
                   ||Substr(l_where, 1, Length(l_where) - 4); 

          dbms_output.Put_line('---------------------------------------'); 

          dbms_output.Put_line(l_sql); 

          EXECUTE IMMEDIATE l_sql INTO l_result; 

          IF l_result > 0 THEN 
            dbms_output.Put_line('Found!!! l_result=' 
                                 ||l_result); 
          END IF; 
        END IF; 
    END LOOP; 
END; 

/ 
1 rows affected

dbms_output:
---------------------------------------
select 
  max(case when "STR" like '%@pqr.de%' then 1 else 0 end)
from "FIDDLE_DBUALQRXFBEQLOXDRJBZ"."T42"
where 
 "STR" is not null
Found!!! l_result=1
---------------------------------------
select 
  max(case when "STR" like '%@pqr.de%' then 1 else 0 end)
from "FIDDLE_DBUALQRXFBEQLOXDRJBZ"."T43"
where 
 "STR" is not null
Found!!! l_result=1
---------------------------------------
select 
  max(case when "COLUMN" like '%@pqr.de%' then 1 else 0 end)
from "FIDDLE_DBUALQRXFBEQLOXDRJBZ"."TABLE"
where 
 "COLUMN" is not null