Datatypes - Expert Oracle Database Architecture - page 525

Oracle throws an error if you attempt to select from the LOB column remotely over a database link:

EODA@ORA12CR1> select * from c@O12CE;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

You can also perform set operation comparisons ( UNION , UNION ALL , MINUS , INTERSECT ) on extended columns,
for example:

EODA@O12CE> select et from t minus select et from t;

Whereas if you tried to compare two LOB columns via a set operator, Oracle returns an error:

EODA@O12CE> select ct from c minus select ct from c;
select ct from c minus select ct from c
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

The prior examples demonstrate that you have more flexibility working with an extended datatype than you
would if working directly with a LOB column. Therefore, if you have an application that deals with character data
greater than 4,000 bytes but less than or equal to 32,727 bytes, then you may want to consider using extended
datatypes. Also, if you're migrating from a non-Oracle database (that supports large character columns) to an Oracle
database, the extended datatype feature will help make that migration easier, as you can now define large sizes for
VARCHAR2 , NVARCHAR2 , and RAW columns natively in Oracle.

Number Types
Oracle 10 g and above supports three native datatypes suitable for storing numbers. Oracle9 i Release 2 and earlier
support exactly one native datatype suitable for storing numeric data. In this list, the NUMBER type is supported by all
releases, and the subsequent two types are new datatypes supported only in Oracle 10 g and above:

•

NUMBER : The Oracle NUMBER type is capable of storing numbers with an extremely large degree
of precision—38 digits of precision, in fact. The underlying data format is similar to a packed
decimal representation. The Oracle NUMBER type is a variable length format from 0 to 22 bytes
in length. It is appropriate for storing any number as small as 10e-130 and numbers up to but
not including 10e126. This is by far the most common NUMBER type in use today.

•

BINARY_FLOAT : This is an IEEE native single-precision floating-point number. On disk it will
consume 5 bytes of storage: 4 fixed bytes for the floating-point number and 1 length byte. It is
capable of storing numbers in the range of ~ ± 10 38.53 with 6 digits of precision.

•

BINARY_DOUBLE : This is an IEEE native double-precision floating-point number. On disk it will
consume 9 bytes of storage: 8 fixed bytes for the floating-point number and 1 length byte. It is
capable of storing numbers in the range of ~ ± 10 308.25 with 13 digits of precision.

As you can see from this quick overview, the Oracle NUMBER type has significantly larger precision than the
BINARY_FLOAT and the BINARY_DOUBLE types, but a much smaller range than the BINARY_DOUBLE . That is, you can
store numbers very precisely with many significant digits in a NUMBER type, but you can store much smaller and larger