Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

  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.

 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
Stores any kind of data in binary format.
Typically used for multimedia data such as images, audio, and video.

 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;
            It's similar to a CLOB, but characters are stored in a NLS or multibyte national character set.
 SQL> create table nclob_1 (col1 number, col2 nclob);
 Table created.

SQL> insert into nclob_1 values(1,'any nchar literal');
 1 row created.


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


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction