Large Object (LOB) Datatypes with Example.

•The LOB datatypes are used to store large and unstructured data such as text, image, video, and spatial data.

•Oracle can store large objects in both internally and externally.

•Oracle built-in LOB datatypes BLOB, CLOB, and NCLOB store data internally and built-in BFILE datatype store data externally.

•The size of BLOB, CLOB, and NCLOB data can be up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). If LOBs are stored in 8K block sized tablespace and if you have used the default value of the CHUNK parameter of LOB storage when creating a LOB column then maximum size of LOB can be =(4GB-1)*8KB

•BFILE data can be up to power(2,31)-1 bytes.

BFILE Datatype
-----------------------
•Suppose a LOB file is exist in OS file system that is outside of oracle database. Then to access of that file you will use BFILE datatype.

•A BFILE column or attribute stores a BFILE locator, which serves as a pointer to the LOB file on the OS file system. The locator maintains the directory name and the filename.

•The BFILE datatype enables read-only support of large binary files. The column defined as BFILE can't be modified or can't be replicated.

An example is here about how we can access LOB data externally by BFILE datatype.
How to Insert Blob data(image, video) into oracle and determine LOB size

BLOB Datatype
--------------------------
•To store binary file internally into a database we can use BLOB datatype.
•The column defined as BLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

An example is here about how we can store LOB data in to oracle database using BLOB.
How to Insert Blob data(image, video) into oracle and determine LOB size

CLOB Datatype
----------------------------
•To store a large character of strings we can use CLOB datatype.
•The column defined as CLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

NCLOB Datatype
-----------------------------------
•The NCLOB datatype stores Unicode data. Both fixed-width and variable-width character sets are supported, and both use the national character set.

•The column defined as NCLOB datatype have fully transactional support that is they can modified, committed, rolled back and can be replicated.

Example of CLOB and NCLOB
-----------------------------------
SQL> CREATE TABLE WITH_LOB(clob_dt CLOB, nclob_dt NCLOB);
Table created.

SQL> insert into with_lob values('This is Clob','This is Nclob');
1 row created.

SQL> select * from with_lob;
CLOB_DT
--------------------------------------------------------------------------------
NCLOB_DT
--------------------------------------------------------------------------------
This is Clob
This is Nclob

Related Documents:
--------------------------------
How to Insert Blob data(image, video) into oracle and determine LOB size

Advantages of LOB against LONG- Feature of LOB datatype

How to move LOB data to another tablespace

How to Insert Blob data(image, video) into oracle BLOB size

Char, Varchar2, Long etc Datatype Limits in Oracle