在数据库的所有表中搜索值 [英] Search value in all tables of database
问题描述
我发现了两个查询。我试过的第一个查询：
$ p $ declare
l_pattern varchar2（100）：='Покакомувопросузвонили？';
cursor cf is select table_name，column_name from user_tab_columns where data_type ='VARCHAR2'order by table_name;
t_str varchar2（2000）：='select count（*）from dual where exists（from $;
l_str varchar2（2000）;
l_where_clause varchar2（2000） ：='where 1 = 0';
l_last_table varchar2（100）：='';
l_cnt number：= 0;
过程query_（i_txt varchar2）是
l_txt varchar2（4000）：= i_txt;
l_ln number：= length（l_txt）;
l_pieces number：= ceil（l_ln / 250）;
begin
for i in 1..l_pieces循环
dbms_output.put_line（substr（i_txt，1 + 250 *（i-1），least（250，l_ln-250 *（i-1））））;
end循环;
dbms_output.new_line;
end;
begin
for cf in loop
if l_last_table<> i.table_name then
- dbms_output.put_line（l_str || l_where_clause）;
立即执行l_str || l_where_clause ||'）'到l_cnt;
if l_cnt> 0 then
query_（'select * from'|| l_last_table || l_where_clause ||';'）;
end if;
l_cnt：= 0;
l_where_clause：='where 1 = 0';
end if;
l_last_table：= i.table_name;
l_str：= t_str || i.table_name;
l_where_clause：= l_where_clause || 'OR'|| i.column_name || 'like'''|| l_pattern ||'''';
结束循环;
立即执行l_str || l_where_clause ||'）'到l_cnt;
if l_cnt> 0 then
query_（'select * from'|| l_last_table || l_where_clause ||';'）;
end if;
end;
这会返回错误：
从命令行1开始的错误：
declare
...
end;
错误报告：ORA-06502：PL / SQL：数字或值错误：字符
字符串缓冲区太小ORA-06512：在行35
06502. 00000 - PL / SQL：数字或数值错误％s
*原因：
*操作：
select * from MV_CATALOG_ITEM where 1 = 0或者代码如'Покакому
或者像'По
какомувопросузвонили？'这样的'PARUTITUDEUUID'，或者像'Покакомувопросу$'这样的CATALOGTITLE'或者像'По
какомувопросузвонили？'这样的CATALOGUUID，或'FOLDERUUID'如'Покакому вопросу
звонили？';
select * from MV_HIERARCHICAL_TEMPLATES where 1 = 0 OR STRINGCONTENT2
like'Покакомувопросузвонили？'或STRINGCONTENT3 like'Покакому
вопросузвонили？'或IDENTIFIER like'Покакому вопросузвонили？'OR
TEMPLATEUUID like'Покакомувопросузвонили？ '或'ATTRIBUTETITLE'如
'Покакомувопросузвонили？'或STRINGCONTENT like'Покакомувопросу
звонили？'或GROUPTITLE like'Покакомувопросузвонили？'or INSTUUID
like'Покакомувопросу或者像'Покакомувопросузвонили？'或'GROUPUUID
'like'Покакомувопросузвонили？'这样的类似ECODE;
我试过的第二个查询：
<$ p
$ value $（
）（
xmltype（
$）从$（$ select $ table $，$ b $ column_name，
to_number）中选择table_name，
column_name
dbms_xmlgen.getxml（
'select count（*）c from'|| table_name ||
'where to_char（'|| column_name ||'）=''JONES'''
$ b $ c
$ utc
where data_type in（'CHAR'，'VARCHAR2'））
where cnt> = 0
这个返回一个大的表和列和错误列表：
从第1行开始的错误在c ommand：
select table_name，
...
where cnt> = 0
错误报告：
SQL错误：ORA-19202：发生错误在XML处理中
ORA-00936：缺少表达式
ORA-06512：在SYS.DBMS_XMLGEN，行176
ORA-06512：在行1
19202. 00000 - XML处理％s发生错误
*原因：处理XML函数
时发生错误操作：检查给定的错误消息并修复相应的问题
我自己找不到方法。
解决方案谢谢大家！以前的脚本执行得非常慢。
我得到了这个脚本的价值：
$ $ p $ $ $ $ $ $ DECLARE
match_count integer;
v_search_string varchar2（4000）：='advcgtfs000080000ict1mosqiomujrk';
EGIN
FOR t IN（SELECT所有者，
table_name，
列名
从all_tab_columns
WHERE data_type in（'VARCHAR2'））
LOOP
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT（*）FROM'|| t.owner || '。'|| t.table_name ||
'WHERE'|| t.column_name ||'=：1'
INTO match_count
USING v_search_string;
IF match_count> 0 THEN
dbms_output.put_line（t.owner ||'。|| || t.table_name ||''|| t.column_name ||''|| match_count）;
END IF;
EXCEPTION
当其他的时候
dbms_output.put_line（'遇到错误'||
t.column_name ||'from || || b $ b t.owner ||'。'|| t.table_name）;
END;
END LOOP;
END;
/
I need to find table which contains some value in my Oracle database.
I found two queries. The first query I tried:
declare
l_pattern varchar2(100) := 'По какому вопросу звонили?';
cursor cf is select table_name,column_name from user_tab_columns where data_type = 'VARCHAR2' order by table_name;
t_str varchar2(2000) := 'select count(*) from dual where exists( select null from ';
l_str varchar2(2000);
l_where_clause varchar2(2000) := ' where 1=0';
l_last_table varchar2(100) := '';
l_cnt number := 0;
procedure query_ (i_txt varchar2) is
l_txt varchar2(4000) := i_txt;
l_ln number := length(l_txt);
l_pieces number := ceil(l_ln/250);
begin
for i in 1..l_pieces loop
dbms_output.put_line(substr(i_txt, 1+250*(i-1),least(250,l_ln-250*(i-1))));
end loop;
dbms_output.new_line;
end;
begin
for i in cf loop
if l_last_table <> i.table_name then
-- dbms_output.put_line(l_str||l_where_clause);
execute immediate l_str || l_where_clause ||')' into l_cnt;
if l_cnt > 0 then
query_ ('select * from ' || l_last_table || l_where_clause || ';');
end if;
l_cnt := 0;
l_where_clause := ' where 1=0';
end if;
l_last_table := i.table_name;
l_str := t_str || i.table_name;
l_where_clause := l_where_clause || ' OR ' || i.column_name || ' like ''' ||l_pattern||'''';
end loop;
execute immediate l_str || l_where_clause ||')' into l_cnt;
if l_cnt > 0 then
query_ ('select * from ' || l_last_table || l_where_clause || ';');
end if;
end;
This returns error:
Error starting at line 1 in command:
declare
...
end;
Error report: ORA-06502: PL/SQL: numeric or value error: character
string buffer too small ORA-06512: at line 35
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
select * from MV_CATALOG_ITEM where 1=0 OR CODE like 'По какому
вопросу звонили?' OR UUID like 'По какому вопросу звонили?' OR
TITLE like 'По какому вопросу звонили?' OR PARENTITEMUUID like 'По
какому вопросу звонили?' OR CATALOGTITLE like 'По какому вопросу
звонили?' OR FOLDERTITLE like 'По какому вопросу звонили?' OR
CATALOGUUID like 'По какому вопросу звонили?' OR CATALOGCODE like 'По
какому вопросу звонили?' OR FOLDERUUID like 'По какому вопросу
звонили?';
select * from MV_HIERARCHICAL_TEMPLATES where 1=0 OR STRINGCONTENT2
like 'По какому вопросу звонили?' OR STRINGCONTENT3 like 'По какому
вопросу звонили?' OR IDENTIFIER like 'По какому вопросу звонили?' OR
TEMPLATEUUID like 'По какому вопросу звонили? ' OR ATTRIBUTETITLE like
'По какому вопросу звонили?' OR STRINGCONTENT like 'По какому вопросу
звонили?' OR GROUPTITLE like 'По какому вопросу звонили?' OR INSTUUID
like 'По какому вопросу звонили?' OR OBJUUID like 'По какому вопросу
звонили?' OR TYP ECODE like 'По какому вопросу звонили?' OR GROUPUUID
like 'По какому вопросу звонили?';
The second query I tried:
select table_name,
column_name
from( select table_name,
column_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml(
'select count(*) c from ' || table_name ||
' where to_char(' || column_name || ') = ''JONES'''
)
),
'ROWSET/ROW/C'
)
) cnt
from (select utc.*, rownum
from user_tab_columns utc
where data_type in ('CHAR', 'VARCHAR2') ) )
where cnt >= 0
This one returns a big list of tables and columns and error:
Error starting at line 1 in command:
select table_name,
...
where cnt >= 0
Error report:
SQL Error: ORA-19202: Error occurred in XML processing
ORA-00936: missing expression
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
I can't find a way myself.
Thanks for all! Previous script implemented very slowly. I got my value with this script:
DECLARE
match_count integer;
v_search_string varchar2(4000) := 'advcgtfs000080000ict1mosqiomujrk';
BEGIN
FOR t IN (SELECT owner,
table_name,
column_name
FROM all_tab_columns
WHERE data_type in ('VARCHAR2') )
LOOP
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line( 'Error encountered trying to read ' ||
t.column_name || ' from ' ||
t.owner || '.' || t.table_name );
END;
END LOOP;
END;
/
这篇关于在数据库的所有表中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
