过滤all_views上的“文本”列

Is there any way I could filter the text column on oracle's all_views table?

有什么办法可以过滤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)

4 个解决方案

#1

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.

希望有所帮助。

#2

You can't manipulate LONG columns easily in SQL unfortunately.

不幸的是，您无法在SQL中轻松操作LONG列。

For your present problem, as a workaround, you could use the *_DEPENDENCIES views 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';

#3

On Oracle Developer run and export to Excel

在Oracle Developer上运行并导出到Excel

SELECT view_name, text from all_views where owner = 'MyDb'

SELECT view_name，来自all_views的文本，其中owner ='MyDb'

Import the Excel file to SQL Server (to [ALL_VIEWS] table)

将Excel文件导入SQL Server（到[ALL_VIEWS]表）

SELECT [VIEW_NAME], [TEXT] FROM [MyDb].[dbo].[ALL_VIEWS] WHERE [TEXT] LIKE '%FOO%'

SELECT [VIEW_NAME]，[TEXT] FROM [MyDb]。[dbo]。[ALL_VIEWS] WHERE [TEXT] LIKE'％FOO％'

#4

Search on TEXT_VC instead of TEXT

搜索TEXT_VC而不是TEXT

SELECT * 
 FROM ALL_VIEWS 
 WHERE UPPER(TEXT_VC) LIKE '%FOO%';

#1