declare
 
 
l_cnt number;
 
 
begin
for i in ( 
            select column_name from ALL_TAB_COLUMNS where owner = 'CREATOR' AND TABLE_NAME LIKE UPPER('%dbt_reserve%')
         )
loop
select count(1) into l_cnt
From CREATOR.dbt_reserve t
where arcdate between date '2013-09-30' - 60 and date '2013-09-30' + 60
and ISAPPROVED=1
and upper(i.column_name) = upper('dealid'); --to_char(2581365) ;
if l_cnt > 0 then
raise_application_error(-20001, l_cnt || ' ' || i.column_name);
end if;
end loop;
end;

SQL> set serveroutput on;

SQL> DECLARE
  2      lcount NUMBER;
  3      lquery VARCHAR2(200);
  4  BEGIN
  5      FOR data IN (SELECT *
  6                   FROM   user_tab_columns) LOOP
  7          lquery := 'select count(*) from '
  8                    ||data.table_name
  9                    ||' where '
 10                    ||data.column_name
 11                    ||' like ''%SCOTT%''';
 12
 13          EXECUTE IMMEDIATE lquery INTO lcount;
 14
 15          IF lcount > 0 THEN
 16            dbms_output.Put_line(data.column_name
 17                                 ||'-----'
 18                                 ||data.table_name);
 19          END IF;
 20      END LOOP;
 21  END;
 22  /
ENAME-----EMP

PL/SQL procedure successfully completed.

SQL>

SELECT *
  FROM (SELECT deptno ||'-'|| dname ||'-'|| loc VALUE
          FROM dept)
 WHERE VALUE LIKE '%10%';

set serveroutput on;

DECLARE
    lcount NUMBER;
    lquery VARCHAR2(200);
BEGIN
    FOR data IN (
                 SELECT * FROM   ALL_TAB_COLUMNS
                 where TABLE_NAME LIKE UPPER('dbt_reserve') 
                   and COLUMN_NAME NOT IN ('MFDEALID')
                ) 
     LOOP
        lquery := 'select count(*) from ' --creator.dbt_reserve'
                  ||data.owner
                  ||'.'  
                  ||data.table_name
                  ||' where ISAPPROVED = 1 AND ARCDATE = DATE ''2013-09-30'' AND  '
                  ||'(' 
                  || data.column_name
                  ||' like ''127638'''
                  ||' OR ' 
                  || data.column_name
                  ||' like ''127639'''
                  ||')' ;

         EXECUTE IMMEDIATE lquery INTO lcount;

          IF lcount > 0 THEN
             RAISE_APPLICATION_ERROR(-20001, LCOUNT || '----' || DATA.COLUMN_NAME || '-----------' || DATA.TABLE_NAME);
--            dbms_output.Put_line(data.column_name
--                                 ||'-----'
--                                 ||data.table_name);
          END IF;
     END LOOP;
END;