Oracle搜索视图文本 [英] Oracle search text of views

问题描述

我有1000多个视图，我想运行一个搜索，该搜索将在其SQL中显示包含字符串abc的视图的名称.如何搜索所有存储过程/SQL(包括视图中的存储过程/SQL)?当我运行命令时:

I have over 1000 views and I want to run a search which will display the names of the views containing the string abc in its SQL. How do I search all stored procedures/SQL including that of my views? When I run the command:

SELECT *
FROM   all_source
WHERE  text LIKE '%abc%'

它返回给我源代码，其中包含字符串abc.但这不包括视图.

it returns me source code in which the string abc is present. But this does not include views.

推荐答案

在12c中，您可以使用它更容易

This gets easier in 12c, where you can use

select *
from   all_views v
where  lower(v.text_vc) like '%abc%';

这假定您要查找的文本字符串的前4000个字符.您可能还会使报告包含text_length> 4000的所有视图以及警告.

This assumes the text string you are looking for is in the first 4000 characters. You might also have the report include any view where text_length is > 4000 along with a warning.

在早期版本中(或为了避免4000个字符的限制)，您可以尝试这样的PL/SQL循环:

In earlier versions (or to avoid the 4000 character restriction), you might try a PL/SQL loop like this:

begin
    dbms_output.put_line('Owner                          View name');
    dbms_output.put_line('------------------------------ -------------------------------');

    for r in (
        select v.owner, v.view_name, v.text
        from   all_views v
        where  v.owner <> 'SYS'
    )
    loop
        if lower(r.text) like '%abc%' then
            dbms_output.put_line(rpad(r.owner,31) || r.view_name);
        end if;
    end loop;
end;

PL/SQL将SQL LONG值隐式转换为32K PL/SQL字符串.

PL/SQL implicitly converts the SQL LONG value to a 32K PL/SQL string.

(在我的12.2.0.1.0中的测试中，当我的光标包含SYS.DBA_SCHEDULER_RUNNING_JOBS或SYS."_user_stat"时，即使在其他具有较长文本的视图已成功处理的情况下，当select语句中的ORA-06502: PL/SQL: numeric or value error失败，并且ORA-06502: PL/SQL: numeric or value error我不确定为什么.我可能没有看到与此相关的问题.)

(In my tests in 12.2.0.1.0, this failed with ORA-06502: PL/SQL: numeric or value error at the select statement when my cursor included SYS.DBA_SCHEDULER_RUNNING_JOBS or SYS."_user_stat", even though other views with longer text were handled successfully, and I'm not sure why. There may be some issue with this I'm not seeing.)

这篇关于Oracle搜索视图文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！