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.

Long to LOB Conversion

I went on an Oracle 11g release 2 seminar recently. They said that LONG data types are still supported but that Oracle recommends converting them to LOB (i.e. CLOB or NCLOB). I’m not totally convinced by this as they are still using LONG columns themselves e.g. in table SYS.VIEW$, which is one of the underlying tables for the DBA_VIEWS view:

 

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 29 14:27:22 2011

 

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> desc sys.view$

Name                       Null?    Type

-------------------------- -------- ------------------

OBJ#                       NOT NULL NUMBER

AUDIT$                     NOT NULL VARCHAR2(38)

COLS                       NOT NULL NUMBER

INTCOLS                    NOT NULL NUMBER

PROPERTY                   NOT NULL NUMBER

FLAGS                      NOT NULL NUMBER

TEXTLENGTH                          NUMBER

TEXT                                LONG

 

SQL>

 

Converting a LONG to a CLOB is easy:

 

SQL> create table andrews_table

  2  (name varchar2(10),

  3   address long)

  4  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Noddy', '10 High St, Anytown')

  3  /

 

1 row created.

 

SQL> alter table andrews_table modify (address clob)

  2  /

 

Table altered.

 

SQL> desc andrews_table

Name                       Null?    Type

-------------------------- -------- ------------------

NAME                                VARCHAR2(10)

ADDRESS                             CLOB

 

SQL> select * from andrews_table

  2  /

 

NAME       ADDRESS

---------- --------------------

Noddy      10 High St, Anytown

 

SQL>

 

But you need to be certain that you want to do this as there is no going back:

 

SQL> alter table andrews_table modify (address long)

  2  /

alter table andrews_table modify (address long)

                                  *

ERROR at line 1:

ORA-22859: invalid modification of columns

 

SQL>

Long Datatypes

I was looking at LONG datatypes in an Oracle 9 database recently. There are a couple of restrictions with them. You cannot do a create table as select or CTAS on a table or view which contains 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> create table andrews_views
  2  as select * from dba_views
  3  /
as select * from dba_views
          *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

SQL>

And you cannot have more than 1 LONG column in a table:

SQL> create table andrews_table
  2  (long1 long)
  3  /

Table created.

SQL> alter table andrews_table add
  2  (long2 long)
  3  /
(long2 long)
*
ERROR at line 2:
ORA-01754: a table may contain only one column of type LONG

SQL>