Geek Answers Handbook

How to include USER_VIEWS.TEXT column in where clause

It seemed to be easy to understand, but I'm struggling to find the answers.

I want to be able to query the USER_VIEWS table in Oracle to find other views that use a specific table.

Something like:

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

I get the error: ORA-00932: inconsistent data types: expected NUMBER received LONG

The data type for TEXT MUST be, and in TOAD, it shows WIDEMEMO.

I tried pouring it, to_char and concatenating. I tried to create another table with TEXT data only, and I get ORA-00997: illegal use of the LONG data type.

Any ideas?

Thanks!

+2
sql oracle oracle10g ora-00932 ora-00997
source share
3 answers

Technically, you can use the DBMS_METADATA package to get the DDL for presentation in the CLOB, and then analyze that it is looking for a link to your table. But there are much simpler solutions than finding a definition.

Oracle maintains object dependency information in the USER_DEPENDENCIES (or ALL_DEPENDENCIES or DBA_DEPENDENCIES depending on your privilege levels and whether it tries to track dependencies between schemas). You are much better off using these 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

If you use the USER_DEPENDENCIES , you can also do more complex things with the dependent object tree. If I create a second view that depends on the first, I can easily see that both views end up using 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
+6
source share

You cannot use LIKE with LONG columns. You can write your own function to do the search, however - see http://www.techonthenet.com/oracle/questions/long_value.php You can also create a table and convert the LONG column to the CLOB column:

 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

+2
source share

If you just want to see it in the TOAD datagrid, you can enable the preview:

View => Toad Parameters => Data Grids => Data => [x] View CLOB and LONG Data

I am using TOAD 10.5.1.3

0
source share

More articles:


All Articles