Large Oracle Objects
datatype can store a maximum of 2,000 characters, while the
datatype has a limit of 4,000 characters.
If you want to store larger amounts of data, you can specify one of the following
The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme, generally called a character set or code page.
The database's character set is established when the database is created.
Examples of character sets are
- 7-bit ASCII (American Standard Code for Information Interchange),
- EBCDIC (Extended Binary Coded Decimal Interchange Code),
- and Unicode UTF-8.
Oracle supports both single-byte and multibyte encoding schemes.
|Stores up to 4 gigabytes of binary data. Oracle does not interpret the values entered into a
|Stores up to 4 gigabytes of character data. Oracle interprets data in a
CLOB as characters.
|Stores up to 4 gigabytes of NLS data.
Versions of Oracle prior to Oracle8 also support the
datatypes, which served the same function.
There are many restrictions on the use of
, such as the inability to select or sort on the values within the
To store large objects and have more control over their content, you can use the Oracle interMedia
Out-of-line data is also used to store large objects (LOBs) that are stored internally (as opposed to BFILEs, which are pointers to LOBs external to the database). In the next chapter, you willsee how to create and manipulate LOB values. The combination of object types, object views, collectors, and LOBs provides a strong foundation for the implementation of an object-relational database application.
Oracle Database 12c
Four types of LOBs are supported:
|BLOB Binary LOB.
||Binary data stored in the database.
|CLOB Character LOB.
||Character data stored in the database.
|BFILE Binary File.
||Read-only binary data stored outside the database, the length of which is limited by the operating system.
||A CLOB column that supports a multibyte character set.
You can create multiple LOBs per table. For example, suppose you want to create a PROPOSAL table to track formal proposals you submit.
Your proposal records may consist of a series of word processing files and spreadsheets used to document and price the proposed work.
The PROPOSAL table will contain VARCHAR2 datatypes (for columns such as the name of the proposal recipient) plus LOB datatypes (containing the word processing and spreadsheet files).
Note: You cannot create multiple LONG or LONG RAW columns in a single table.
The create table command in the following listing creates the PROPOSAL table:
create table PROPOSAL
constraint PROPOSAL_PK primary key (Proposal_ID));
An option for the Oracle database designed for handling specific types of large data (such as images or spatial data) and text.