Storing and Reading in LOBS in oracle

•  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.