The LOB datatypes (OraBLOB/OraCLOB and OraBFILE) enable you to store large blocks of unstructured data, such as text, graphic
images, video clips, and sound waveforms, up to four gigabytes in size. They
provide efficient, random, piece-wise access to the data.
·Retrieving LOBs from the Database
·Performance Considerations with LOB Read and Write
·Writing LOB Data·Reading LOB Data
LOB datatypes differ from LONG and LONG RAW datatypes in several ways:
· A table can contain multiple LOB columns but only one LONG column.
· A table containing one or more LOB columns can be partitioned, but a table
containing a LONG column cannot be partitioned.
· The maximum size of a LOB is four gigabytes, but the maximum size of a LONG is
two gigabytes.
· LOBs support random access to data, but LONGs support only sequential access.
· LOB datatypes (except NCLOB) can be attributes of a user-defined object type
but LONG datatypes cannot.
· LOB client side buffering is available to optimize for multiple small writes.
· LOB data can be stored in operating system files outside of database
tablespaces (BFILES).
The LOBs datatype is further sub divided into following types:
· BLOB - a LOB whose value is composed of unstructured binary ("raw") data.
· CLOB - a LOB whose value is composed of single-byte fixed-width character data
that corresponds to the database character set defined for the Oracle
database.
· NCLOB - a LOB whose value is composed of fixed-width multi-byte character data
that corresponds to the national character set defined for the Oracle
database.
· BFILE - a LOB whose large binary data is stored in operating system files
outside of database tablespaces. They may also be located on tertiary storage
devices such as hard disks, CD-ROMs, PhotoCDs, and DVDs
In Oracle Objects for OLE, an instance of the BLOB datatype is represented as
an OraBlob interface, a CLOB/NCLOB datatype is represented as an OraClob
interface, and a BFILE datatype is represented as an OraBFile interface.
The following example creates a table having BLOB and CLOB columns and inserts
some rows into it using ExecuteSQL method on OraDatabase object.
Set OO4OSession = CreateObject("OracleInProcServer.XOraSession")
Set InvDb = OO4OSession.OpenDatabase("INVDB", "scott/tiger", 0)
InvDb.ExecuteSQL("create table part(part_id NUMBER, part_name
VARCHAR2(20),part_image BLOB, part_desc CLOB)")
InvDb.ExecuteSQL ("insert into part values (1,'ORACLE
NETWORK',EMPTY_BLOB(),EMPTY_CLOB())")
InvDb.ExecuteSQL ("insert into part values (2,'ORACLE SERVER',
EMPTY_BLOB(),EMPTY_CLOB())")
The EMPTY_BLOB() and EMPTY_CLOB() PL/SQL functions provide an empty LOB to
insert into the LOB column.
