Oracle поиск текстовых просмотров

Question

У меня более 1000 просмотров, и я хочу запустить поиск, который будет отображать имена представлений, содержащих строку abc в своем SQL. Как мне найти все хранимые процедуры /SQL, включая мои представления? Когда я запускаю команду:

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

он возвращает мне исходный код, в котором строка abc настоящее. Но это не включает взгляды.

Answer 1

Это становится проще в 12с, где вы можете использовать

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

Это предполагает, что текстовая строка, которую вы ищете, находится в первых 4000 символов. Вы также можете включить в отчет любое представление, где text_length > 4000 вместе с предупреждением.

В более ранних версиях (или во избежание ограничения в 4000 символов) вы можете попробовать цикл PL/SQL следующим образом:

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.

(В моих тестах в 12.2.0.1.0 это не удалось с ORA-06502: PL/SQL: numeric or value error на select заявление, когда мой курсор включен SYS.DBA_SCHEDULER_RUNNING_JOBS или же SYS."_user_stat", хотя другие представления с более длинным текстом были обработаны успешно, и я не уверен почему. Там может быть какая-то проблема с этим я не вижу.)

Answer 2

Выбрать ALL_VIEWS вместо (имя столбца TEXT также).

Хотя вы бы предпочли использовать один из UPPER или же LOWER функционирует как

select *
from all_views
where lower(text) like '%abc%'

потому что однажды вы могли бы поставить его как "abc", в другой раз "ABC" и т. д.

[РЕДАКТИРОВАТЬ, из-за ORA-00932]

Ах да - в ALL_VIEWS столбец TEXT имеет тип данных LONG (тогда как в ALL_SOURCE это VARCHAR2, поэтому LIKE не будет работать на ALL_VIEWS.

Один из вариантов - создать "временную" таблицу, которая содержит все представления и применить функцию TO_LOB к столбцу TEXT, а затем выбрать из нее:

SQL> create or replace view my_emp as select empno, ename xxx_ename, job from emp;

View created.

SQL> create table my_all_views as
  2  select owner, view_name, to_lob(text) text
  3  from all_views;

Table created.

SQL> select owner, view_name
  2  from my_all_views
  3  where lower(text) like '%xxx%';

OWNER                          VIEW_NAME
------------------------------ ------------------------------
SYS                            USER_SCHEDULER_JOB_DESTS
SYS                            ALL_SCHEDULER_JOB_DESTS
SYS                            USER_XML_SCHEMAS
SYS                            ALL_XML_SCHEMAS
SYS                            ALL_XML_SCHEMAS2
SCOTT                          MY_EMP

6 rows selected.

SQL>

Его недостаток в том, что он не масштабируется; если вы создадите новое представление, вам придется воссоздать таблицу.

Или вы можете создать свою собственную функцию, которая будет выполнять этот поиск. Например:

SQL> create or replace function f_search_view (par_string in varchar2)
  2    return sys.odcivarchar2list
  3    pipelined
  4  is
  5  begin
  6    for cur_r in (select view_name, text from all_views
  7                  where text_length < 32767)
  8    loop
  9      if instr(cur_r.text, par_string) > 0 then
 10         pipe row(cur_r.view_name);
 11      end if;
 12    end loop;
 13
 14    return;
 15  end;
 16  /

Function created.

SQL> select * from table(f_search_view('xxx'));

COLUMN_VALUE
--------------------------------------------------------------------------------
USER_XML_SCHEMAS
ALL_XML_SCHEMAS
ALL_XML_SCHEMAS2
MY_EMP

SQL>