oracle 在数据库的所有表中搜索值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17591007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search value in all tables of database
提问by Akmal Salikhov
I need to find table which contains some value in my Oracle database.
我需要在我的 Oracle 数据库中找到包含一些值的表。
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.
我自己也找不到办法。
回答by Akmal Salikhov
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;
/
回答by Alex Poole
The code you previously posted as an answer (and haven't moved to the question for some reason) will almost do what you want as a fairly simple version; replicating it here with mostly minor, but one crucial, changes:
您之前作为答案发布的代码（并且由于某种原因没有转向问题）几乎可以作为一个相当简单的版本来满足您的需求；在这里复制它，其中大部分是次要但至关重要的变化：
SET SERVEROUTPUT ON SIZE 100000
DECLARE match_count INTEGER;
v_owner all_tab_cols.owner%type := 'NAUCRM';
v_data_type all_tab_cols.data_type%type :='VARCHAR2';
v_search_string VARCHAR2(4000) := 'PC_Number';
BEGIN
FOR t IN (SELECT table_name, column_name
FROM all_tab_cols
WHERE owner = v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||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.table_name ||' '||t.column_name
||' '||match_count );
END IF;
END LOOP;
END;
/
The crucial change, as I already mentioned in comments, is that the owner has to be in uppercase (unless your user was built really strangely!), so 'NAUCRM'rather then 'naucrm'.
正如我在评论中已经提到的，关键的变化是所有者必须是大写的（除非您的用户构建非常奇怪！），'NAUCRM'而不是'naucrm'.
