У меня более 1000 представлений, и я хочу запустить поиск, который отобразит имена представлений, содержащих строку abc
в своем SQL. Как выполнить поиск по всем хранимым процедурам / SQL, включая мои представления? Когда я запускаю команду:
SELECT *
FROM all_source
WHERE text LIKE '%abc%'
он возвращает мне исходный код, в котором присутствует строка abc
. Но сюда не входят просмотры.
Вместо этого выберите 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>
Я отредактировал свое сообщение, добавив несколько примеров; взглянуть.
Большое спасибо!! Работает по первому способу.
Это становится проще в 12c, где вы можете использовать
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
в 32-килобайтную строку PL / SQL.
(В моих тестах в 12.2.0.1.0 это не удалось с ORA-06502: PL/SQL: numeric or value error
в операторе select
, когда мой курсор включал SYS.DBA_SCHEDULER_RUNNING_JOBS
или SYS."_user_stat"
, хотя другие представления с более длинным текстом обрабатывались успешно, и я не уверен, почему. Могут быть некоторые проблемы с этим я не вижу.)
Спасибо. Пытался запустить это. Получил ошибку: ORA00932-несовместимые типы данных: ожидаемый символ стал длинным. Есть идеи, почему?