Lobs are a powerful way for storing, accessing, and maintaining large content in oracle database.
A LOB can be up to 128 terabytes or more in size depending on your block sizeStore both binary and character content such as text, graphic images, still video clips, full motion video, and sound.
Type of LOBs
Internal LOB
Those stored in the database either in-line in the table or in a separate segment or tablespace.
CLOBs-Character Large Object
BLOBs-Binary Large Object
NCLOBs-National Character Large Object
External LOB
Those stored as operating system files or outside of database.
BFILE-Binary file
CLOB
Stores string data in the database character set format. Used for large strings or documents that uses the database character set exclusively.
Characters in the database character set are in a fixed width format.
Examples
SQL> CREATE TABLE lob1 (col1 number, col2 clob);
Table created.
SQL> insert into lob1 values (1,234);
1 row created.
SQL> insert into lob1 values (2,’hi frnds how r u’);
1 row created.
SQL> select * from lob1;
COL1 COL2
———- ——————————————————————————–
1 234
2 hi frnds how r u
BLOB
Stores any kind of data in binary format.
Typically used for multimedia data such as images, audio, and video.
Examples
SQL> CREATE TABLE lob2 (col1 number, col2 blob);
Table created.
SQL> insert into lob2 values(12,utl_raw.cast_to_raw(‘/home/oracle/1.png’));
1 row created.
SQL> select count(*) from lob2;
COUNT(*)
———-
1
NCLOB
It’s similar to a CLOB, but characters are stored in a NLS or multibyte national character set.
Example
SQL> create table nclob_1 (col1 number, col2 nclob);
Table created.
SQL> insert into nclob_1 values(1,’any nchar literal’);
1 row created.
BFILE
A binary file stored outside of the database in the host operating system file system, but accessible from database tables.
Bfiles can be accessed from your application on a read-only basis.
Use Bfiles to store static data, such as image data, that does not need to be manipulated in applications.
Examples
create directory dir_1 as ‘/home/oracle/mydir/’;
Directory created.
SQL> grant read,write on directory dir_1 to public;
Grant succeeded.
SQL> create table bfile_1 (col1 number,col2 bfile);
Table created.
SQL> insert into bfile_1 values (1, bfilename(‘dir_1′,’1.png’));
1 row created.
postgres 
