PL/SQL - Large objects

VARCHAR2 can store max 4000 bytes data then what next? LOB's or Large object comes into to overcome the Varchar2 limit.

LOB's:

• LOB's or large objects help to store large data.
• It can also store unstructured data such as video, audio, photo images, etc within the database.

LOB's are catwgorized into 2 types:

• Intetnal LOB's
• External LOB's

Internal LOB's:

Internal LOBs are stored withon the database as columns in a table and participate the transaction mode of the server.

• ClOB:  A character LOB, used to store single byte character data. Max size: 8TB to 128 TB.
• BLOB: A binary LOB, used to store binary  raw data. Max size: 8 TB to 128 TB.
• NCLOB: a LOB used to store multi byte character data. Max size: 8TB to 128 TB.

External LOB's:

External LOB's are stored outside of the database as operating system files, only a reference to the actual OS file is stored in the db. External LOBs do not participate in transactions.

BFILE:

BFILE refers to Binary file. The BFILE LOB holds reference to large binary data stored as physical files in the OS outside the db.

Max size: 4 GB

Max size of file name: 255 characters.

Max size of a directory name: 30 characters.

LOB's Restrictions:

• LOBs cant be a part of a clustered table.
• LOBs cant be used in the following part of a SQL statement.
• GROUP BY
• ORDER BY
• SELECT DISTINCT
• JOINS
• AGGREGATE functions
• LOBs cant be analyzed using the Analyze cmd 
• LOBs cant be included in a partitioned index organized table
• LOBs cant be used in VARRAYs.

LOB Locator:

• Every LOB is associated with a lob locator.
• A lob locator is a pointet to the actual location of the LOB value.
• The locator associated with internal LOBs is called a LOB locator while the locator associated with external LOBs os called a BFILE locator.
• When storing data in a LOB column, we are also storing a LOB locator with it.
• This LOB locator is what is returned to us when we select the LOB column.
• The actual LOB valur can then be retrieved using this locator.

Character Large Object (CLOB):

Create a table job_resumes

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume clob

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', ' resume details....');

We can use varius SQL functions on CLOBs. We should use normal functions only on relatively small CLOBs, upto about 100K in size. When we are querying larger LOBs, we sould use the DBMS_LOB package functions for better performances:

Select substr(reume, 1, 30) from job_resumes;

Select DBMS_LOB.substr(reume, 1, 30) from job_resumes;

Select length(resume), dbms_lob.getlength(resume) from job_reumes;

Binary Large Object(BLOB):

• CLOBs can store only character data but BLOBs can store any type of data.
• They are used to hold non-traditional data such as videos, voice, images, pdf, documents etc.
• Even we can insert character data into a BLOB , oracle will store it but it will be in raw data. That means we cant use SQL functions like substr to handle BLOB data.

Create a table job_resumes1

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

resume blob

);

Storing images/videos in BLOBs:

Create a table job_resumes2

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture blob

);

CREATE DIRECTORY MYIMAGES AS 'C:\MYIMAGES';

DECLARE

SRC BFILE := BFILENAME('MYIMAGES','SRI.JPG,');

DEST BLOB;

BEGIN

INSERT INTO JOB_RESUMES2 VALUES(1, 'SRI' ,'M', EMPTY_BLOB()) RETURNING PROFILE_PICTURE INTO DEST;

Dbms_lob.open(src,dbms_lob.lob_readonly);

Dbms_lob.loadfromfile(dest,src,dbms_lob.getlength(src));

Dbms_lob.close(src);

Commit;

End;

/

Binary File(BFILE):

BFile refers to Binary file. This BFILE LOB holds references to large binary data stored as physical files in the OS outside db. They do not participate in the transactions.

Create a table job_resumes3

(

resume_id number,

First_name varchar2(100),

Last_name varchar2(100),

Profile_picture BFILE

);

INSERT INTO JOB_RESUMES values(1, 'Sri' 'Das', BFILENAME('MYIMAGES','SRI.JPG'));