LOB and BFILE

// Large Objects Types: (LOBs)
--> BFILE,BLOB,CLOB and NCLOB
--> They allow U to store blocks of unstructured data such as text, graphic images, video clips and sound waves, upto 4 GB in size
--> LOBs also alow random, piece wise access to the data
--> LOB is made up of two distinct parts - Value and Locator
--> Value is the actual data that will be stored
--> Locater is an indicator that specifies the location of the object in the database
--> A column based on LOB type is called as LOB Column
--> The LOB column stores the locator of the object
--> The LOBs can be stored inside the db (Internal LOB or in-line) or outside the db (External LOB or off-line) ie., in the os files

a) BFILE:
--> Used to store large binary objects in os files outside db if its size is > 4 GB, inside db if size is <4 GB

b) BLOB :
--> To store Large binary objects inside the db (Upto 4 GB)

c) CLOB:
--> CLOB is similar to LONG datatype, except that CLOB are used to store large blocks of single-byte character data in the db

d) NCLOB:
--> is used to store large blocks of single-byte or multi byte NCHAR data in the database based on the National Language Character set

// Managing Large Objects:

i) Creating table with LOB columns :
Ex: Create table airbus_desc (airbusno char(5), airbus_det bfile, airbus_profile clob);

ii) Inserting values in LOBs:
--> To insert value in the BFILE, the function bfilename is used.
--> It takes the os path of the directory and the name of the file
Ex: Insert into airbus_desc values ('AB01',bfilename('c:\orant\bin\desc_dir','airbus_desc.doc'), 'The description of the plane is as follows:');

iii) Displaying data from LOBs :
--> Data from LOBs cannot be displayed, except for CLOB by using Select statment
Ex:
Select airbusno_airbus_profile from airbus_desc;