--------------------

-- 这里是查询 数字型字段值

/*declare

CURSOR cur_query IS

  select table_name, column_name, data_type from user_tab_columns;

  a number;

  sql_hard varchar2(2000);

  vv number;

begin

  for rec1 in cur_query loop

  a:=0;

  if rec1.data_type ='NUMBER' THEN

  a := 1;

  end if;

  if a>0 then

  sql_hard := '';

  sql_hard := 'SELECT count(*) FROM  '|| rec1.table_name ||' where '

  ||rec1.column_name|| '=''TYRCE0BF26AB5C586B3 '' ';

  dbms_output.put_line(sql_hard);

  execute immediate sql_hard INTO vv;

  IF vv > 0 THEN

   dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);

  end if;

  END IF;

  end loop;

end;*/

declare

CURSOR cur_query IS

  select a.table_name, a.column_name, a.data_type from user_tab_columns a

         where 1=1

         and (lower(a.data_type) = 'varchar2' or lower(a.data_type) = 'char')

         and NOT a.TABLE_NAME like '%$%'

         and NOT a.TABLE_NAME like '%+%'

         and NOT a.TABLE_NAME like '%=%'

         ORDER BY A.TABLE_NAME ASC

  ;

  a number;

  sql_hard varchar2(2000);

  vv number;

  rscount number;

  str varchar2(2000);

  num number;

  findValue varchar2(500) :='R2186E3DC09B88E1AF  ';   -- 要查询的字符串值

begin

  rscount:=0;

    str:='TRUNCATE table tmp_test';

  execute immediate str;  

  str:='drop table tmp_test';

  execute immediate str;  

  str:='CREATE GLOBAL TEMPORARY TABLE tmp_test (tab_name varchar2(500), col_name varchar2(500))  ON COMMIT PRESERVE ROWS';

  execute immediate str;    ----使用动态SQL语句来执行

  for rec1 in cur_query loop

        rscount:= rscount + 1;

        a:=0;

        --if rec1.data_type ='VARCHAR2' or rec1.data_type='CHAR' THEN

        if rec1.data_type ='VARCHAR2' or rec1.data_type='CHAR' THEN

        a := 1;

        end if;

        if a>0 then

              sql_hard := '';

              sql_hard := 'SELECT count(*) FROM  '|| rec1.table_name ||' where '

              --||rec1.column_name|| ' =''wu''';

              ||rec1.column_name|| ' ='''|| findValue || '''';

              --dbms_output.put_line(sql_hard);

              --dbms_output.put_line( '**** ' || rec1.table_name||'--'||rec1.column_name);

              execute immediate sql_hard INTO vv;

              IF NVL(vv,0) > 0 THEN

               dbms_output.put_line(rec1.table_name||'--'||rec1.column_name);

                  str:= 'insert into tmp_test '

                        || ' select ''' || rec1.table_name ||  ''' as tab_name,''' || rec1.column_name || ''' as col_name from dual';

                  execute immediate str;

                  str:= 'commit';

                  execute immediate str;

              end if;

        END IF;

  end loop;

 --str:= 'select * from tmp_test';

 --execute immediate str;

end;

-- 运行完以后 执行

/*

select * from tmp_test;

*/