DECLARE
	CURSOR T_EMAIL IS
	select table_name,column_name from user_tab_cols where data_type='VARCHAR2' AND TABLE_NAME NOT IN('BONUS');
	v_stmt varchar2(3000);
	
	R_DATA	USER_TAB_COLS.COLUMN_NAME%TYPE;
	TYPE Ref_crs IS REF CURSOR;
	DATA_CV	Ref_crs;
	v_DATA  VARCHAR2(3000);
BEGIN
	FOR R_RECORD IN T_EMAIL LOOP
		BEGIN

		V_STMT:='SELECT '||R_RECORD.COLUMN_NAME||' FROM '||R_RECORD.TABLE_NAME||' WHERE lower('||R_RECORD.COLUMN_NAME||') LIKE ''%@%.com''';		
		--dbms_output.put_line(v_stmt);
		OPEN DATA_CV FOR v_stmt;
		LOOP 	
			FETCH DATA_CV INTO v_DATA;
			EXIT WHEN DATA_CV%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(R_RECORD.TABLE_NAME||'-->'||V_DATA);
		END LOOP;
		CLOSE DATA_CV;
		EXCEPTION
			WHEN others THEN
				--dbms_output.put_line(v_stmt);
				NULL;
		END;
	END LOOP;
END;
/

SQL> drop table temp1
  2  /

Table dropped.

SQL> 
SQL> create table temp1
  2  (
  3   a number not null primary key
  4  ,b varchar2(20)
  5  ,c varchar2(20)
  6  ,d varchar2(20)
  7  )
  8  /

Table created.

SQL> 
SQL> insert into temp1 values (1,'abc.def.ghi.jkl','123.456.789.jkl','jkl')
  2  /

1 row created.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> select count(*)
  2  from temp1
  3  where b like '%jkl%'
  4  or c like '%jkl%'
  5  or d like '%jkl%'
  6  /

  COUNT(*)
----------
         1

1 row selected.

SQL> 
SQL> select 'select count(*)' TEXT from user_tables where table_name = 'TEMP1'
  2  union all
  3  select 'from '||table_name TEXT from user_tables where table_name = 'TEMP1'
  4  union all
  5  select 'where '||column_name||' like ''%jkl%''' from user_tab_columns where table_name = 'TEMP1' and data_type = 'VARCHAR2'
  6  union all
  7  select '/' from user_tables where table_name = 'TEMP1'
  8  /

TEXT
-------------------------------------------------
select count(*)
from TEMP1
where B like '%jkl%'
where C like '%jkl%'
where D like '%jkl%'
/

6 rows selected.

SQL> 
SQL> with
  2      first_column as (
  3                        select min(column_id) column_id
  4                        from user_tab_columns
  5                        where table_name = 'TEMP1'
  6                        and data_type = 'VARCHAR2'
  7                      )
  8  select 'select count(*)' TEXT from user_tables where table_name = 'TEMP1'
  9  union all
 10  select 'from '||table_name TEXT from user_tables where table_name = 'TEMP1'
 11  union all
 12  select case when first_column.column_id = user_tab_columns.column_id then 'where ' else 'or ' end
 13          ||column_name||' like ''%jkl%''' from user_tab_columns,first_column where table_name = 'TEMP1' and data_type = 'VARCHAR2'
 14  union all
 15  select '/' from user_tables where table_name = 'TEMP1'
 16  /

TEXT
-------------------------------------------------
select count(*)
from TEMP1
where B like '%jkl%'
or C like '%jkl%'
or D like '%jkl%'
/

6 rows selected.

SQL> select count(*)
  2  from TEMP1
  3  where B like '%jkl%'
  4  or C like '%jkl%'
  5  or D like '%jkl%'
  6  /

  COUNT(*)
----------
         1

1 row selected.