Oracle Datatypes

As shown below, Oracle supports a rich variety of datatypes, including most of the SQL2003 datatypes.

BFILE (SQL2003 Datatype: DATALINK)

Holds a pointer to a BLOB stored outside the database, but present on the local server, of up to 4 GB in size. The database streams input (but not output) access to the external BLOB. If you delete a row containing a BFILE value, only the pointer value is deleted. The actual file structure is not deleted.

BINARY_FLOAT (SQL2003 Datatype: FLOAT)

Holds a 32-bit floating point number.

BINARY_DOUBLE (SQL2003 Datatype: FLOAT)

Holds a 64-bit floating point number.

BLOB (SQL2003 Datatype: BLOB)

Holds a binary large object (BLOB) value of between 8 and 128 terabytes in size, depending on the database blocksize. In Oracle, large binary objects (BLOBs, CLOBs, and NCLOBs) have the following restrictions:

  • They cannot be selected remotely.

  • They cannot be stored in clusters.

  • They cannot compose a varray.

  • They cannot be a component of an ORDER BY or GROUP BY clause in a query.

  • They cannot be used by an aggregate function in a query.

  • They cannot be referenced in queries using DISTINCT, UNIQUE, or joins.

  • They cannot be referenced in ANALYZE...COMPUTE or ANALYZE...ESTIMATE statements.

  • They cannot be part of a primary key or index key.

  • They cannot be used in the UPDATE OF clause in an UPDATE trigger.

CHAR(n)[BYTE | CHAR], CHARACTER(n)[BYTE | CHAR] (SQL2003 Datatype: CHARACTER(n))

Holds fixed-length character data up to 2,000 bytes in length. BYTE tells Oracle ...

Get SQL in a Nutshell, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.