How to include the column USER_VIEWS.TEXT in a where clause

Question

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.

Something like:

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

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

The datatype for TEXT is LONG and in TOAD it shows 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.

Any ideas?

Thanks!

Answer 1

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 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

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