How to manipulate LOB large objects ?

[ezhilarasan_1]

Hi,

How to manipulate LOB large objects ?
The object types CLOB, BLOB, BFILE,
LONG, RAW, LONG RAW ...

As a develper, also as DBA, how to manage
these objects.
Any special care needed while doing export
& import, or any limitations in select or
in any DML statments, ...

Please guide me, I am new to this area.

Thanks
Ezhil

[Indira Aramandla]

Hi Ezhil,

LOBs can be broadly categorized as Internal LOBs and External LOBs. Internal LOBS can store binary data (BLOBs), single-byte character data (CLOBs), or multi-byte character data (NCLOBs).

External LOBs (referred to as BFILES), are stored in operating system files outside the database tablespaces. These LOBs do not participate in transactions

There are 4 types of LOB:
CLOB, BLOB, NCLOB stored internally to Oracle
BFILE stored externally
Data stored in a LOB is called the LOB's value. To the Oracle8 Server, a LOB's value is unstructured and cannot be queried against. LOBs can be stored along with other row data or separate from row data. Regardless of how the data is stored, every LOB has a locator associated with it which can be viewed as a handle or pointer to the actual location. Selecting a LOB returns the LOB locator instead of the LOB value. Two new special functions in Oracle8 SQL DML, EMPTY_BLOB() and EMPTY_CLOB(), allow initialization of NULL or non-NULL LOB columns to empty.

PL/SQL provides a mechanism to manipulate these LOBs via the DBMS_LOB package

The DBMS_LOB package provides functions and procedures which allow manipulation
of specific parts as well as complete internal LOBs and read-only operations on BFILEs.

The LOBs can also be manipulated using OCI. Here is a comparison of the DBMS_LOB package procedures and the OCI functions.

You can export/import LOB data. Export/import with the above data types restrictions are described in the Oracle8i uilities

1. Import with INDEXFILE parameter to generate the script that can be used to modify the LOB's tablespace clause.

$imp system/manager fromuser=scott tables=\(circul\)
indexfile=create_lob_table
2. Edit the generated script file create_lob_table.sql:

3. Run the script create_lob_table

4. Import the data only in the created table, ignoring the CREATE TABLE statement failure.

Attached is a document from metalink describing manipulations with LOBS.

I hope this helps

Indira A