Large Oracle Objects
The
CHAR datatype can store a maximum of 2,000 characters, while the
VARCHAR datatype has a limit of 4,000 characters.
If you want to store larger amounts of data, you can specify one of the following
LOB datatypes:
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.
BLOB |
Stores up to 4 gigabytes of binary data. Oracle does not interpret the values entered into a BLOB column. |
CLOB |
Stores up to 4 gigabytes of character data. Oracle interprets data in a CLOB as characters. |
NLOB |
Stores up to 4 gigabytes of NLS data. |
Versions of Oracle prior to Oracle8 also support the
LONG and
LONG RAW datatypes, which served the same function.
There are many restrictions on the use of
LOBs, such as the inability to select or sort on the values within the
LOB column.
To store large objects and have more control over their content, you can use the
Oracle interMedia[1] option.
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
Available Datatypes
Four types of LOBs are supported:
| LOB Datatype |
Description |
| 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. |
| NCLOB |
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
(Proposal_ID NUMBER(10),
Recipient_Name VARCHAR2(25),
Proposal_Name VARCHAR2(25),
Short_Description VARCHAR2(1000),
Proposal_Text CLOB,
Budget BLOB,
Cover_Letter BFILE,
constraint PROPOSAL_PK primary key (Proposal_ID));
[1]
Oracle interMedia: An option for the Oracle database designed for handling specific types of large data (such as images or spatial data) and text.
