如何在where子句中包含USER_VIEWS.TEXT列 [英] How to include the column USER_VIEWS.TEXT in a where clause

问题描述

这似乎应该很容易弄清楚，但我一直在努力寻找任何答案.

This seems like it should have been an easy thing to figure out but I am struggling to find any answers.

我希望能够查询Oracle中的USER_VIEWS表，以查找正在使用特定表的其他视图.

I want to be able to query against the USER_VIEWS table in Oracle to find other views that are using a particular table.

类似的东西:

选择视图名称，文本来自用户视图 文字在哪里，例如'％MY_TABLE％'

SELECT view_name, text FROM user_views WHERE text LIKE'%MY_TABLE%'

我得到了错误: ORA-00932:数据类型不一致:预期的编号长了

I get the error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG

TEXT的数据类型为LONG，在TOAD中显示为WIDEMEMO.

The datatype for TEXT is LONG and in TOAD it shows WIDEMEMO.

我尝试过将其转换为to_char并进行连接.我尝试仅使用TEXT数据创建另一个表，但得到ORA-00997:非法使用LONG数据类型.

I have tried casting it, to_char and concatenating. I tried creating another table with just the TEXT data and I get ORA-00997: illegal use of LONG datatype.

有什么想法吗?

谢谢！

推荐答案

从技术上讲，您可以使用DBMS_METADATA包在CLOB中获取该视图的DDL，然后对其进行解析以查找对表的引用.但是比查看视图定义要容易得多的解决方案.

Technically, you could use the DBMS_METADATA package to get the DDL for the view in a CLOB and then parse that looking for a reference to your table. But there are far easier solutions than looking at the view definition.

Oracle在USER_DEPENDENCIES视图(或ALL_DEPENDENCIES或DBA_DEPENDENCIES)中维护有关对象依赖关系的信息，具体取决于您的特权级别以及是否要跟踪跨架构的依赖关系.您最好使用这些视图

Oracle maintains information about object dependencies in the USER_DEPENDENCIES view (or ALL_DEPENDENCIES or DBA_DEPENDENCIES depending on your privilege levels and whether you're trying to track dependencies across schemas). You're far better off using those views

SQL> create table base_table (
  2    col1 number
  3  );

Table created.

SQL> create view my_view
  2  as
  3  select *
  4    from base_table;

View created.

SQL> select name, type
  2    from user_dependencies
  3   where referenced_name = 'BASE_TABLE';

NAME                           TYPE
------------------------------ ------------------
MY_VIEW                        VIEW

如果使用的是USER_DEPENDENCIES视图，则还可以对依赖对象树进行更复杂的操作.如果我创建一个依赖于第一个视图的第二个视图，则可以很容易地看到两个视图最终都使用了基表.

If you're using the USER_DEPENDENCIES view, you can also do more sophisticated things with the tree of dependent objects. If I create a second view that depends on the first, I can easily see that both views eventually use the base table.

SQL> create view my_view2
  2  as
  3  select *
  4    from my_view;

View created.

SQL> ed
Wrote file afiedt.buf

  1  select level, name, type
  2    from user_dependencies
  3  start with referenced_name = 'BASE_TABLE'
  4* connect by referenced_name = prior name
SQL> /

     LEVEL NAME                           TYPE
---------- ------------------------------ ------------------
         1 MY_VIEW                        VIEW
         2 MY_VIEW2                       VIEW

这篇关于如何在where子句中包含USER_VIEWS.TEXT列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！