Oracle 中 根据值 查询 所在 表和字段

---------------------- 这里是查询 数字型字段值
/*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;
*/