Database Reference
In-Depth Information
Table 12-2.
Long Types Compared to LOBs
LONG/LONG RAW Type
CLOB/BLOB Type
You may have only one
LONG
or
LONG RAW
column per table.
You may have up to 1,000 columns of
CLOB
or
BLOB
type per table.
User-defined types may fully use
CLOB
and
BLOB
types.
User-defined types may not be defined with attributes of type
LONG
/
LONG RAW
.
LONG
types may not be referenced in the
WHERE
clause.
LOB
s may be referenced in the
WHERE
clause,
and a host of functions is supplied in the
DBMS_LOB
package to manipulate them.
LONG
types do not support distributed transactions.
LOB
s do support distributed transactions.
LONG
types cannot be replicated using basic or advanced replication.
LOB
s fully support replication.
LONG
columns cannot be in a
GROUP BY
,
ORDER BY
, or
CONNECT BY
, or
in a query that uses
DISTINCT
,
UNIQUE
,
INTERSECT
,
MINUS
, or
UNION
.
LOB
s may appear in these clauses provided
a function is applied to the
LOB
that converts
it into a scalar SQL type (contains an atomic
value) such as a
VARCHAR2
,
NUMBER
, or
DATE
.
PL/SQL functions/procedures cannot accept an input of type
LONG
.
PL/SQL works fully with
LOB
types.
SQL built-in functions cannot be used against
LONG
columns
(e.g.,
SUBSTR
).
SQL functions may be used against
LOB
types.
You cannot use a
LONG
type in a
CREATE TABLE AS SELECT
statement.
LOB
s support
CREATE TABLE AS SELECT
.
You cannot use
ALTER TABLE MOVE
on a table containing
LONG
types.
You may move tables containing
LOB
s.
As you can see, Table
12-2
presents quite a long list; there are many things you just cannot do when you
have a
LONG
column in the table. For all new applications, do not even consider using the
LONG
type. Instead, use
the appropriate
LOB
type. For existing applications, you should seriously consider converting the
LONG
type to
the corresponding
LOB
type if you are hitting any of the restrictions in Table
12-2
. Care has been taken to provide
backward compatibility so that an application written for
LONG
types will work against the
LOB
type transparently.
■
It almost goes without saying that you should perform a full functionality test against your application(s) before
modifying your production system from
LONG
to
LOB
types.
Note
Coping with Legacy LONG Types
A question that arises frequently is, “What about the data dictionary in Oracle?” It is littered with
LONG
columns, and
this makes using the dictionary columns problematic. For example, it is not possible using SQL to search the
ALL_VIEWS
dictionary view to find all views that contain the text
HELLO:
EODA@ORA12CR1> select *
2 from all_views
3 where text like '%HELLO%';
where text like '%HELLO%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
