SQL 在 all_views 上过滤“文本”列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9228812/
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
Filter "text" column on all_views
提问by Vitor Freitas
Is there any way I could filter the textcolumn on oracle's all_viewstable?
有什么办法可以过滤oracle的all_views表上的文本列吗？
For example:
例如：
SELECT *
FROM ALL_VIEWS
WHERE UPPER(TEXT) LIKE '%FOO%';
Exception:
例外：
ORA-00932: inconsistent datatypes: expected NUMBER got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Edit:
编辑：
DESC ALL_VIEWS
Name Null Type
---------------- -------- --------------
OWNER NOT NULL VARCHAR2(30)
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG()
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
回答by tbone
You can't convert to a clob on the fly via a select statement unfortunately. to_lob function works with INSERT statements, but that would mean you'd need to setup a separate table and do inserts into using to_lob.
不幸的是，您无法通过 select 语句即时转换为 clob。to_lob 函数适用于 INSERT 语句，但这意味着您需要设置一个单独的表并使用 to_lob 进行插入。
You can do assignment conversions to varchar in pl/sql, and most of the time you'll find that the text_length in all_views is < 32767, so this will cover "most" cases, although its not a nice as just selecting:
您可以在 pl/sql 中对 varchar 进行赋值转换，并且大多数情况下您会发现 all_views 中的 text_length < 32767，因此这将涵盖“大多数”情况，尽管它不像仅选择那样好：
declare
l_search varchar2(1000) := 'union';
l_char varchar2(32767);
begin
for rec in (select * from all_views where text_length < 32767)
loop
l_char := rec.text;
if (instr(l_char, l_search) > 0) then
dbms_output.put_line('Match found for ' || rec.owner || '.' || rec.view_name);
end if;
end loop;
end;
Here I'm searching the text field for the string 'union'.
在这里，我在文本字段中搜索字符串“union”。
Hope that helps.
希望有帮助。
回答by Vincent Malgrat
You can't manipulate LONG columns easily in SQL unfortunately.
不幸的是，您无法在 SQL 中轻松操作 LONG 列。
For your present problem, as a workaround, you could use the *_DEPENDENCIESviews to find all views dependent upon a table:
对于您目前的问题，作为一种解决方法，您可以使用*_DEPENDENCIES视图查找依赖于表的所有视图：
SELECT *
FROM all_dependencies
WHERE type = 'VIEW'
AND referenced_owner = 'TABLE_OWNER'
AND referenced_name = 'YOUR_TABLE';
回答by user2785110
Search on TEXT_VC instead of TEXT
搜索 TEXT_VC 而不是 TEXT
SELECT *
FROM ALL_VIEWS
WHERE UPPER(TEXT_VC) LIKE '%FOO%';
回答by user10576512
If you are on a pre 12c (text_vconly exists in 12.1+) and you can sufficiently scan your views through the first 4000 characters the following query will work.
如果您使用的是 pre 12c（text_vc仅存在于 12.1+ 中）并且您可以通过前 4000 个字符充分扫描您的视图，则以下查询将起作用。
with parsed_all_views as (
select owner, view_name, text_length, TEXT as VARCHAR_TEXT
from xmltable( '/ROWSET/ROW' passing dbms_xmlgen.getXMLType(
'select TEXT, owner, view_name, text_length '
||' from all_views WHERE owner = ''**YOUR_SCHEMA_NAME**'' '
) columns TEXT varchar2(4000), owner varchar2(32), view_name
varchar2(32), text_length varchar2(32))
)
select * from parsed_all_views where upper(VARCHAR_TEXT) like '%DISTINCT%'
回答by Igor Krupitsky
On Oracle Developer run and export to Excel
SELECT view_name, text from all_views where owner = 'MyDb'
在 Oracle Developer 上运行并导出到 Excel
SELECT view_name, text from all_views where owner = 'MyDb'
Import the Excel file to SQL Server (to [ALL_VIEWS] table)
SELECT [VIEW_NAME], [TEXT] FROM [MyDb].[dbo].[ALL_VIEWS] WHERE [TEXT] LIKE '%FOO%'
将 Excel 文件导入 SQL Server（到 [ALL_VIEWS] 表）
SELECT [VIEW_NAME], [TEXT] FROM [MyDb].[dbo].[ALL_VIEWS] WHERE [TEXT] LIKE '%FOO%'
