Friday, July 13, 2012


This was tested on Oracle 9 and Oracle 11.2. If you are searching for a piece of text, which you believe is in one of your views, you might try to do it like this:

SQL> select owner, view_name
  2  from dba_views
  3  where upper(text) like '%BLAH%'
  4  /
where upper(text) like '%BLAH%'
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got


It fails because TEXT is a LONG column:

SQL> desc dba_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)


One workaround is to SELECT the OWNERVIEW_NAME and TEXT of all your views and SPOOL them to a file. Then you can search the spool file produced with vi or Notepad.

No comments:

Post a Comment