ORA-00932

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

LONG

SQL>

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)

SQL>

One workaround is to SELECT the OWNER, VIEW_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.