oracle 如何在 where 子句中包含列 USER_VIEWS.TEXT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5543314/
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
How to include the column USER_VIEWS.TEXT in a where clause
提问by northpole
This seems like it should have been an easy thing to figure out but I am struggling to find any answers.
这似乎应该是一件很容易弄清楚的事情，但我正在努力寻找任何答案。
I want to be able to query against the USER_VIEWS table in Oracle to find other views that are using a particular table.
我希望能够查询 Oracle 中的 USER_VIEWS 表以查找使用特定表的其他视图。
Something like:
就像是：
SELECT view_name, text FROM user_views WHERE text LIKE'%MY_TABLE%'
SELECT view_name, text FROM user_views WHERE text LIKE'%MY_TABLE%'
I get the error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
我收到错误消息：ORA-00932：不一致的数据类型：预期 NUMBER 得到了 LONG
The datatype for TEXT is LONG and in TOAD it shows WIDEMEMO.
TEXT 的数据类型是 LONG，在 TOAD 中它显示 WIDEMEMO。
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.
我试过铸造它，to_char 和连接。我尝试使用 TEXT 数据创建另一个表，但我得到 ORA-00997：非法使用 LONG 数据类型。
Any ideas?
有任何想法吗？
Thanks!
谢谢！
回答by Justin Cave
Technically, you could use the DBMS_METADATApackage 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.
从技术上讲，您可以使用该DBMS_METADATA包获取 CLOB 中视图的 DDL，然后解析它以查找对您的表的引用。但是有比查看视图定义更简单的解决方案。
Oracle maintains information about object dependencies in the USER_DEPENDENCIESview (or ALL_DEPENDENCIESor DBA_DEPENDENCIESdepending on your privilege levels and whether you're trying to track dependencies across schemas). You're far better off using those views
甲骨文保持在大约对象相关信息的USER_DEPENDENCIES视图（或ALL_DEPENDENCIES或DBA_DEPENDENCIES根据您的权限级别，以及是否你想跟踪所有模式的依赖）。你最好使用这些视图
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
If you're using the USER_DEPENDENCIESview, 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.
如果您正在使用USER_DEPENDENCIES视图，您还可以使用依赖对象树做更复杂的事情。如果我创建依赖于第一个视图的第二个视图，我可以很容易地看到两个视图最终都使用了基表。
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
回答by Frank Schmitt
You cannot use LIKE with LONG columns. You could write your own custom function to perform the search, though - see http://www.techonthenet.com/oracle/questions/long_value.phpYou could also create a table and convert the LONG column to a CLOB column:
不能对 LONG 列使用 LIKE。不过，您可以编写自己的自定义函数来执行搜索 - 请参阅http://www.techonthenet.com/oracle/questions/long_value.php您还可以创建一个表并将 LONG 列转换为 CLOB 列：
create table my_tab as
select to_lob(text) from user_views;
see also http://www.dba-oracle.com/oracle_news/2005_5_9_converting_long_lob_data_types.htm
另见http://www.dba-oracle.com/oracle_news/2005_5_9_converting_long_lob_data_types.htm
回答by Andrew
If you just want to see it in TOAD's datagrid then you can turn on the preview:
如果您只想在 TOAD 的数据网格中看到它，那么您可以打开预览：
View => Toad Options => Data Grids => Data => [x] Preview CLOB and LONG data
查看 => Toad 选项 => 数据网格 => 数据 => [x] 预览 CLOB 和 LONG 数据
I'm using TOAD 10.5.1.3
我正在使用 TOAD 10.5.1.3
