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

 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.

Comments

Popular posts from this blog

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools