This document provides an in-depth look at Oracle LOBs. It tells you what LOBs are, why you need them, how to create and manipulate LOBs and also looks at the DBMS_LOB package that is supplied with Oracle to work with LOBs.

What are LOBs: -

A LOB is a Large Object. LOBs are use to store large, unstructured data, such as video, audio, photo images etc. With a LOB you can store up to 4 Gigabytes of data. They are similar to a LONG or LONG RAW but differ from them in quite a few ways.

Why use LOBs and not LONG or LONG RAW: -

LOBs offer more features to the developer than a LONG or LONG RAW. The main differences between the data types also indicate why you would use a LOB instead of a LONG or LONG RAW. These differences include the following: -

You can have more than one LOB column in a table, whereas you are restricted to just one LONG or LONG RAW column per table.

When you insert into a LOB, the actual value of the LOB is stored in a separate segment (except for in-line LOBs) and only the LOB locator is stored in the row, thus making it more efficient from a storage as well as query perspective. With LONG or LONG RAW, the entire data is stored in-line with the rest of the table row.

LOBs allow a random access to its data, whereas with a LONG you have to go in for a sequential read of the data from beginning to end.

The maximum length of a LOB is 4 Gig as compared to a 2 Gig limit on LONG

Querying a LOB column returns the LOB locator and not the entire value of the LOB. On the other hand, querying LONG returns the entire value contained within the LONG column.

LOB types: -

You can have two categories of LOBs based on their location with respect to the database. The categories include internal LOBs and external LOBs. As the names suggest, internal LOBs are stored within the database, as table columns. External LOBs are stored outside the database as operating system files. Only a reference to the actual OS file is stored in the database. An internal LOB can also be persistent or temporary depending on the life of the internal LOB.

An internal LOB can be one of three different data types as follows: -

CLOB – A Character LOB. Used to store character data.

BLOB – A Binary LOB. Used to store binary, raw data

NCLOB – A LOB that stores character data that corresponds to the national character set defined for the database.

The only external LOB data type in Oracle 8i is called a BFILE.

BFILE - Short for Binary File. These hold references to large binary data stored as physical files in the OS outside the database.

LOB Locator: -

Associated with each LOB is a locator. A locator is a pointer 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 is called a BFILE locator. When you store data in a LOB column, you also store a LOB locator with it. This LOB locator is what is returned to you when you query the LOB column, the actual value can then be got using this locator.

A LOB can be initialised to either a NULL value or made empty. The basic difference has to do with the locator. If you set a LOB to NULL, the LOB has no locator or value stored in the column. The value stored will be NULL. An empty LOB on the other hand has a locator as well as data of length 0 stored in the column.

Before you actually start writing data to a LOB column using the various programming interfaces provided by Oracle, you need to make it non-null by populating it with a locator. To do this, use the built-in functions EMPTY_BLOB () for BLOBs, EMPTY_CLOB () for CLOBs and NCLOBs. For BFILEs, use the BFILENAME () method to initialise a BFILE column to point to an OS file. Remember when you query a LOB column, only the locator is returned to you.

Difference between Internal LOBs and External LOBs: -

The major difference between the internal LOBs and external LOBs are as follows: -

Internal LOBs are stored in the database. External LOBs are stored outside the database in operating system files.
Internal LOBs can take part in transactions. In the event of a failure, internal LOBs can be recovered and changes made to them can be committed or rolled back. External LOBs do not participate in transactions. The BFILE type allows only read access to the operating system files. Changes to the external LOBs must be done out with the database through the underlying OS.
Types of internal LOBs include CLOB, NCLOB and BLOB. The only external LOB is a BFILE.
Internal LOBs use copy semantics. That is when you insert/update a LOB with a LOB from another row in the table, the LOB locator as well as the LOB value are copied to the row. External LOBs on the other hand use reference semantics. That is only the BFILE location is copied and not the actual operating system file.
Each internal LOB column has a distinct LOB locator for each row and a distinct copy of the LOB value. Each BFILE column has its own BFILE locator for each row. However you could have two rows in the table that contain BFILE locators pointing to the same operating system file.

Data Definition Language (DDL) commands and LOBs: -

This section tells you how to create a table with LOB columns and also how to alter or modify details of a LOB column in a table.

Creating a Table having LOB columns: -

You can create tables that have one or more LOB columns of the same or different type. When you create a LOB column you also have the option of specifying if you want the data within the LOB to be stored in-line or out-of-line. This is done using the ENABLE | DISABLE STORAGE IN ROW clause. If you enable storage in the row, Oracle stores the LOB value within the row, provided the length of the row (and the locator) is less than 4K. If it changes to more than 4K, then the LOB value will be moved out of the row into the LOB segment. Only the locator is stored within the row.

It is important to note that when you create LOB column(s), you are actually creating separate LOB segments. These LOB segments can be in the same tablespace as your table. However you also have the option of specifying a different tablespace for the LOB segment.

In addition, for each LOB column you create, Oracle implicitly creates a LOB index. This index is maintained by Oracle and you cannot alter or drop it.

The name of the LOB segment defaults to SYS_LOBxxxx, where xxxx is a hexadecimal number. The name of the LOB index defaults to SYS_ILxxxx where xxxx is a hexadecimal number. The hexadecimal numbers for both the LOB segment and the LOB index are the same.

When creating a LOB column you have the option of specifying the name of the LOB segment as well as the LOB index. In addition you also can specify which tablespace they go into, the storage details etc.

To create tables with LOB columns, you specify one of the LOB types as the data type of the column. So, for example, the following statement

CREATE TABLE lobtable

(employee_id NUMBER,

resume CLOB,

comments CLOB);

would create a table lobtable having 3 columns, two of which are LOB columns. For each of these LOB columns, you would have implicitly created a LOB segment and a LOB index. To view information about the LOB columns you’ve created, check out the XXX_LOBS (where XXX could be ALL, DBA or USER) dictionary views. For our lobtable, we find the following information: -

SQL> SELECT table_name "Table", column_name "Column", segment_name "Segment",

2 index_name "Index"

3 FROM user_lobs;

Table	Column	Segment	Index
----------------	------------------	-----------------------------------------	------------------------------------
LOBTABLE	COMMENTS	SYS_LOB0000016119C00002$$	SYS_IL0000016119C00002$$
LOBTABLE	RESUME	SYS_LOB0000016119C00003$$	SYS_IL0000016119C00003$$

SQL>SELECT segment_name, segment_type, tablespace_name

2 FROM user_segments

3 WHERE segment_name like 'SYS_LOB%';

SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME
----------------------------------------	------------------------	------------------------------
SYS_LOB0000016119C00002$$	LOBSEGMENT	USERS
SYS_LOB0000016119C00003$$	LOBSEGMENT	USERS

SQL> SELECT segment_name, segment_type, tablespace_name

2 FROM user_segments

3 WHERE segment_name like 'SYS_IL%';

SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME
----------------------------------------	-------------------------	-----------------------------
SYS_IL0000016119C00002$$	LOBINDEX	USERS
SYS_IL0000016119C00003$$	LOBINDEX	USERS

When creating tables with LOB columns, you can specify the name of the LOB segment, the LOB index, storage characteristics and LOB specific details. This is done using the LOB clause of the CREATE TABLE statement. Although you can specify details for the LOB index, this clause is deprecated as of Oracle 8i. You can still use it however without an error, but it is a good idea to leave it out and let Oracle manage the index details. The general syntax for this is as follows: -

CREATE TABLE <tabname>

(col_list)

[Physical attributes]

[Storage details]

[LOB (<lobcol1> [, <lobcol2>…]) STORE AS [<lob_segment_name>]

([TABLESPACE <name>]

[{ENABLE | DISABLE} STORAGE IN ROW]

[CHUNK <chunk_size>]

[PCTVERSION <version_number>]

[{CACHE | NO CACHE [{LOGGING | NO LOGGING}]

| CACHE READS [{LOGGING | NOLOGGING}]}]

[<storage_clause_for_LOB_segment>]

[INDEX [<lob_ind_name>] [physical attributes] [<storage_for_LOB_index>]]

)

]

[LOB (<lobcol1> [, <lobcol2>….])…]

The LOB clause can be specified for a single LOB or for some/all LOBs in your table. If you specify more than one LOB column in a single LOB clause, you cannot name the LOB segments. You can use the LOB clause with several LOB columns for example if you wanted to specify the same storage or other attributes for all of them.

Using the LOB clause above, we could re-create our lobtable table as follows, specifying the tablespace and other details for one or more LOB columns.

Example 1: - Specifying names for the LOB segment as well as the LOB index for each of the LOB columns

SQL> CREATE TABLE lobtable (employee_id NUMBER,

resume CLOB, comments CLOB)

LOB (comments) STORE AS comments_seg

(TABLESPACE lobtbs

CHUNK 4096 CACHE

STORAGE (MINEXTENTS 2)

INDEX comments_ind (TABLESPACE indxtbs

STORAGE (MAXEXTENTS UNLIMITED)

)

LOB (resume) STORE AS resume_seg

(TABLESPACE lobtbs

ENABLE STORAGE IN ROW

INDEX resume_ind (TABLESPACE indxtbs)

);

SQL> SELECT table_name "TABLE", column_name "COLUMN", segment_name "SEGMENT",
2 index_name "INDEX"
3 FROM user_lobs;

TABLE	COLUMN	SEGMENT	INDEX
------------------	--------------------	-------------------------	-------------------------
LOBTABLE	COMMENTS	COMMENTS_SEG	COMMENTS_IND
LOBTABLE	RESUME	RESUME_SEG	RESUME_IND

Example 2: - Specifying common values for all columns together.

SQL> CREATE TABLE lobtable

(employee_id NUMBER,

resume CLOB,

comments CLOB)

LOB (resume, comments) STORE AS

(CHUNK 4096 TABLESPACE lobtbs

ENABLE STORAGE IN ROW

CACHE

)

In the above case, remember you haven’t given a name to either the LOB segment or the LOB index and they will both default to their default names (SYS_LOBXXX and SYS_ILXXX).

Note: - For details on what the various options within the LOB clause mean, look up the Oracle SQL Reference Manual.

Altering LOB columns in a table: -

Once you create a LOB column in a table, you can modify some parts of its definition. However you cannot change the chunk size value. Neither can you change the settings for ENABLE | DISABLE STORAGE IN ROW, except when moving the table using the MOVE clause. You can change the PCTVERSION, CACHE and LOGGING options as well as the storage details. To change the tablespace information, you can use the MOVE clause of the ALTER TABLE statement.

The syntax for the ALTER TABLE statement is as follows. Please note that this is specific to changes to a LOB column only, and does not include other options.

Syntax: -

ALTER TABLE <tname>

{ADD (<lobcol> <LOBTYPE> <lob_clause_same_as_for_CREATE>) |

MODIFY LOB (<lobcol>) ([<storage_clause>]

[PCTVERSION <version_number>]

[{CACHE | NOCACHE [{LOGGING | NOLOGGING}] |

CACHE READS [{LOGGING | NOLOGGING}]

}]

) |

MOVE [ONLINE] [<physical_attributes>][TABLESPACE <tname>]

[{LOGGING | NOLOGGING}] [<lob_clause_same_as_for_CREATE>]

Example 1: - Altering a table to add a new LOB column

SQL> ALTER TABLE lobtable

ADD (photo BLOB)

LOB (photo) STORE AS photo_seg

(TABLESPACE lobtbs CHUNK 4096 PCTVERSION 5

ENABLE STORAGE IN ROW);

Example 2: - Modifying an existing LOB column in a table

SQL> ALTER TABLE lobtable

MODIFY LOB (photo) (STORAGE (maxextents unlimited) cache);

Manipulating LOBs: -

Initialising LOBs to NULL or empty: -

You can initialise an internal LOB to either a NULL or to be empty. An external LOB column can be initialised to either NULL or to a filename.

You use either the EMPTY_BLOB () or the EMPTY_CLOB () methods to initialise an internal LOB to be empty (see how to insert into LOBs below). These functions can be used even with the DEFAULT clause of the CREATE/ALTER TABLE statements.

Example 1: - Creating a table with default values for the LOB column in it.

SQL> CREATE TABLE lobtable

(employee_id NUMBER,

photo BLOB DEFAULT EMPTY_BLOB(),

resume CLOB DEFAULT EMPTY_CLOB(),

comments CLOB DEFAULT EMPTY_CLOB());

Example 2: - Altering the default values for LOB columns in a table.

SQL> ALTER TABLE lobtable

MODIFY (photo DEFAULT empty_blob (),

resume DEFAULT empty_clob());

Remember, you cannot call any of the functions in the Oracle programming interfaces (such as the PL/SQL package DBMS_LOB and OCI calls) on a LOB that is NULL, because they work on LOB locators. You need to either initialise it to a value or set it to be empty.

DML operations on internal and external LOBs: -

You can use the SQL DML commands including INSERT, UPDATE and DELETE with LOB columns.

As mentioned, if you are using programming interfaces to work with LOBs, you need to have a locator assigned to the LOB column. You can do this using the INSERT statement, which can be used to assign a value, set the column to empty or even set the value to NULL.

To insert NULL values into LOB columns in a row, do the following

SQL> INSERT INTO lobtable

VALUES (1, null, null, null);

To insert either a value or make the LOB empty, do the following

SQL> INSERT INTO lobtable (employee_id, photo, resume, comments)

VALUES (1, empty_blob (), empty_clob (), ‘This is a test’);

When working within PL/SQL, you would normally want to first set the LOB values to empty and then read back the details and make your changes using the lob locator. These values can then be written back into the database. One way of doing this is to first perform an INSERT as above, then SELECT the populated lob locator into a local variable, make your changes using the lob locator and then write the information back. An easier way to accomplish this would be to use the RETURNING clause of the INSERT statement to return the new locator into a variable directly.

An example follows: -

DECLARE L_comments CLOB;
L_photo BLOB;
L_resume CLOB;
BEGIN
INSERT INTO lobtable
VALUES (1, empty_blob (), empty_clob (), empty_clob ())
RETURNING photo, comments, resume INTO
L_photo, l_comments, l_resume;

/* You can then use DBMS_LOB package or whatever to make required changes using the local variables that point to the locators */

COMMIT;

END;

To assign a BFILE column, you will have to use the BFILENAME () function. This function takes two arguments, one the logical directory (which has to be mapped to a physical directory on the OS) and the filename. Before you use the BFILENAME routine, you will have to create a DIRECTORY object.

An example follows: -

SQL> CREATE DIRECTORY lobdir AS ‘C:\mydir\lobs\’;

SQL> CREATE TABLE externalTable

(photo BFILE);

SQL> INSERT INTO externalTable

VALUES (BFILENAME (‘lobdir’,’test.bmp’));

The same concepts you followed for INSERT can also be applied to UPDATE. So you could update a LOB to be empty, null or set it to a value. You can also assign a row value from a SELECT query.

Some examples: -

SQL> UPDATE lobtable

SET comments = empty_clob();

SQL> UPDATE lobtable

SET photo = null;

SQL> UPDATE lobtable

SET comments = (SELECT mycomments FROM myCommentsTable);

SQL> UPDATE externalTable

SET photo = BFILENAME (‘lobdir’,’another.bmp’);

Deleting rows from a table containing LOB columns is no different from deleting any other table. Since you are working on the row as a whole, not an individual column. So you could do the following: -

SQL> DELETE lobtable;

SQL> DELETE lobtable WHERE employee_id = 1;

Remember though, that with BFILEs you are actually only storing references to external files. So when you delete a row containing a BFILE, all you are doing is deleting the BFILE locator. The actual file will still exist on the operating system, till you delete it manually using OS utilities.

Using the provided DBMS_LOB package: -

Oracle provides the DBMS_LOB package for working with LOBs in general. This package contains a whole load of procedures and functions that let you perform quite complex operations using LOBs, including finding a pattern within a LOB, getting a sub-string, opening and closing LOBs in different modes, add/replace content in a LOB etc.

We look at only some of the procedures and functions here. To get a complete listing, either look up the ‘Oracle Supplied PL/SQL Packages’ manual or do a ‘desc dbms_lob’ at the SQL prompt.

Persistent LOBs: -

All DBMS_LOB packages work off a LOB locator. So you must provide a locator that represents a LOB that is either in your database table or on your operating system. For both internal and external LOBs, you must therefore already have initialised your LOB (using either the empty_blob, empty_clob or bfilename functions). You can then use the SELECT statement to fetch the LOB locator into a local variable and work off the locator variable.

One thing to remember when working with the DBMS_LOB package, especially with reference to procedures/functions that modify an internal LOB, is that you have to explicitly lock rows before you perform a modification. The packaged procedures/functions do not lock the row containing the LOB you are trying to modify.

A list of some of the packaged procedures and functions are given below.

Methods for manipulating content of the LOB: -

DBMS_LOB.INSTR (

lob_loc IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

pattern IN {RAW | VARCHAR2 CHARACTER SET lob_loc%CHARSET},

offset IN INTEGER: = 1,

nth IN INTEGER: = 1) RETURN INTEGER;

This method returns the position of the nth occurrence of the patter starting from the specified offset. The pattern cannot include wild-card characters such as those used for the LIKE operator (% or _). If the pattern is not found, a value 0 is returned.

DBMS_LOB.COMPARE (

lob_loc1 IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

lob_loc2 IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

amount IN INTEGER := 4294967295,

offset IN INTEGER := 1,

offset 2 IN INTEGER := 1) RETURN INTEGER;

This function compares two lobs, either part of the lobs or whole lobs. You can only compare LOBs of the same type. It returns 0 if the data matches exactly over the range specified by the offset and amount values. Else a non-zero integer is returned.

DBMS_LOB.SUBSTR (

lob_loc IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

amount IN INTEGER: = 32767,

offset IN INTEGER: = 1)

RETURN {RAW | VARCHAR2 CHARACTER SET lob_lob%CHARSET};

This function returns the sub-string of a LOB data value. The type of the returned VARCHAR2 buffer must match the form of the CLOB parameter.

DBMS_LOB.TRIM (

lob_loc IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

new_length IN INTEGER);

This procedure trims the value of the LOB value to the length you specify. Trying to trim an empty LOB does not do anything. If the new length you specify is greater than the length of the LOB value, an exception is raised.

DBMS_LOB.GETLENGTH (

Lob_loc IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS})

RETURN INTEGER;

This function returns the length of the specified LOB. Remember an empty LOB has a length 0.

DBMS_LOB.COPY (

dest_lob IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

src_lob IN {BLOB | CLOB CHARACTER SET dest_lob%CHARSET},

amount IN INTEGER,

dest_offset IN INTEGER: = 1,

src_offset IN INTEGER:= 1);

This procedure is used to copy all or part of the source internal LOB into the destination internal LOB. If the offset specified for the destination LOB is beyond the end of data currently in the LOB, zero-byte fillers or spaces are inserted into the BLOB or CLOB respectively. If the offset specified is less than current length of the destination LOB, then data in the destination LOB will be overwritten by the new data.

Methods to work on a BFILE: -

The following are methods that are used with external LOBs or BFILE. Not all functions/procedures have been included. Please check out the on-line documentation for a complete listing.

DBMS_LOB.LOADFROMFILE (

Dest_lob IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

Src_lob IN BFILE,

Amount IN INTEGER,

Dest_offset IN INTEGER: = 1,

Src_offset IN INTEGER: = 1);

This procedure copies all or part of the source BFILE into the destination BFILE. This procedure is quite similar to the way COPY works, except that it works with BFILEs

DBMS_LOB.OPEN (

lob_loc IN OUT NOCOPY {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

open_mode IN BINARY_INTEGER);

This procedure opens the given internal or external LOB in one of two modes, either read-only or read-write. The value for the open_mode parameter could be either lob_readonly or lob_readwrite. A BFILE can only be opened in the read-only mode, that is the only valid value for open_mode when working with BFILE is lob_readonly.

DBMS_LOB.FILECLOSE (

file_loc IN OUT NOCOPY BFILE);

DBMS_LOB.FILECLOSEALL;

The above two procedures are used to close an opened BFILE. The first takes in a BFILE locator and closes the BFILE referenced by it. The second function closes all open BFILEs in the current session.

Reading and Writing into LOBs: -

DBMS_LOB.READ (

lob_loc IN {BLOB | BFILE | CLOB CHARACTER SET ANY_CS},

amount IN OUT NOCOPY BINARY_INTEGER,

offset IN INTEGER,

buffer OUT {RAW | VARCHAR2 CHARACTER SET lob_lob%CHARSET};

This procedure reads the specified portion of the LOB and places it in the buffer specified. The form of the VARCHAR2 buffer must match the form of the CLOB parameter.

DBMS_LOB.APPEND (

Dest_lob IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

Src_lob IN {BLOB | CLOB CHARACTER SET dest_lob%CHARSET});

Use the append procedure to add contents of the source LOB into the destination LOB. Obviously you can only append to/from LOBs of the same type, so CLOB to CLOB or BLOB to BLOB.

DBMS_LOB.WRITE (

Lob_loc IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

Amount IN BINARY_INTEGER,

Offset IN INTEGER,

Buffer IN {RAW | VARCHAR2 CHARACTER SET ANY_CS});

The write procedure is used to write a specified amount of data from the buffer into an internal LOB, starting at the offset position. Any data already in the LOB is overwritten. The form of the VARCHAR2 buffer must match the form of the CLOB parameter.

There are many more procedures and functions that haven’t been included in this list. Please check the documentation for a complete list.

Examples for working with persistent LOBs: -

Example 1: -

DECLARE

L_resume CLOB;

L_comments CLOB;

Comm_buf VARCHAR2 (30): = 'Joined company on the 10/10/01';

Resume_buf VARCHAR2 (30): = ‘Resume for Scott’;

BEGIN

/* Create a row in our lobtable, initialising the lob columns to empty and

returning the locator values into local variables */

INSERT INTO lobtable (employee_id, resume, comments)

VALUES (10, empty_clob (), empty_clob ())

RETURNING resume, comments INTO L_resume, L_comments;

-- Now explicitly open both LOB locators in read-write mode

dbms_lob.open(L_resume, dbms_lob.lob_readwrite);

dbms_lob.open(L_comments, dbms_lob.lob_readwrite);

--Write info from the buffers into the LOBs pointed to by the locators

dbms_lob.write(L_resume, length(Resume_buf), 1, Resume_buf);

dbms_lob.write(L_comments, length(Comm_buf), 1, Comm_buf);

--An example of how to use the instr function

IF (dbms_lob.instr (L_resume, 'Scott', 1,1) > 0) THEN

dbms_output.put_line('This is the resume for employee Scott ');

END IF;

--Now print out contents of the

dbms_output.put_line('Printing the contents of the two LOBs');

dbms_output.put_line('L_RESUME = ' || dbms_lob.substr(L_resume,

dbms_lob.getlength(L_resume),1));

dbms_output.put_line('L_COMMENTS = ' || dbms_lob.substr(L_comments,

dbms_lob.getlength(L_comments),1));

--Close opened LOBs before you commit work.

dbms_lob.close(L_resume);

dbms_lob.close(L_comments);

Commit;

END;

The output of this block is

SQL>/

This is the resume for employee Scott
Printing the contents of the two LOBs
L_RESUME = Resume for Scott
L_COMMENTS = Joined company on the 10/10/01

PL/SQL procedure successfully completed.

Example 2: -

DECLARE

l_bfile BFILE;

l_dir VARCHAR2(30);

l_file VARCHAR2(30);

BEGIN

SELECT myfile INTO l_bfile

FROM lobtest;

IF (dbms_lob.fileexists (l_bfile) = 1) THEN

IF (dbms_lob.fileisopen (l_bfile) = 1) THEN

dbms_output.put_line('File is open');

ELSE

dbms_lob.open(l_bfile, dbms_lob.lob_readonly);

END IF;

dbms_output.put_line('Length of file is ' || dbms_lob.getlength(l_bfile));

ELSE

dbms_lob.filegetname(l_bfile, l_dir, l_file);

dbms_output.put_line('File with name ' || l_file || ' does not exist in directory '|| l_dir);

END IF;

END;

The output of this PL/SQL code is as follows: -

SQL> /

Length of file is 23

PL/SQL procedure successfully completed.

Temporary LOBs: -

Oracle provides you with the functionality of creating temporary LOBs. As the name suggests, they are not permanent and hence not stored in database tables. They normally exist for the duration of a user session. Though you can, at the time of creation, specify that you want the temporary LOB to exist only for the duration of the current program call. You normally use them like you would use local variables, you need them to perform some operations on LOB data without having to store it in the database.

You can create, access, update and then free your temporary LOBs. There is no logging or redo information generated for temporary LOBs, thus giving you better performance.

Temporary LOBs are created in your temporary tablespace. You use DBMS_LOB.CREATETEMPORARY() procedure to create a temporary LOB. When you create a temporary LOB you are automatically setting it to empty. You cannot use the EMPTY_BLOB or EMPTY_CLOB functions with temporary LOBs.

The following procedures and functions are provided to you as part of the DBMS_LOB package to work with temporary LOBs.

Creating a temporary LOB: -

DBMS_LOB.CREATETEMPORARY (

lob_loc IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS},

cache IN BOOLEAN

duration IN PLS_INTEGER := DBMS_LOB.SESSION);

This procedure creates a temporary LOB with the locator returned in lob_loc. In addition, it also creates a temporary LOB index in the default temporary tablespace. The duration parameter specifies the lifetime of the temporary LOB and defaults to the session. If you want, you can also set it to the current program call using the integer DBMS_LOB.CALL.

Freeing the temporary LOB: -

DBMS_LOB.FREETEMPORARY (

lob_loc IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS});

This procedure frees the created temporary CLOB or BLOB in your temporary tablespace. Once you call this procedure, the lob locator associated with the temporary LOB is marked invalid. If you subsequently assign this lob locator to another lob locator, the latter one is also freed and marked invalid.

Checking if a LOB is temporary: -

DBMS_LOB.ISTEMPORARY (

Lob_loc IN {BLOB | CLOB CHARACTER SET ANY_CS})

RETURN integer;

Use this function to determine if a given lob locator points to a temporary or persistent LOB. It returns an integer value: 1 for a temporary LOB and 0 for a persistent LOB.

An example: -

DECLARE

tempLOB CLOB;

amt NUMBER := 14;

position NUMBER := 1;

buffer VARCHAR2(20) := 'this is a test';

BEGIN

/*Create and open a temporary lob for reading and writing */

DBMS_LOB.CREATETEMPORARY (tempLOB, true);

IF (DBMS_LOB.ISTEMPORARY (tempLOB) = 1) THEN

dbms_output.put_line('A temporary LOB has been created');

ELSE

dbms_output.put_line('Not a temporary LOB');

END IF;

DBMS_LOB.OPEN (tempLOB, DBMS_LOB.LOB_READWRITE);

/* write the buffer data into the temporary LOB*/

DBMS_LOB.WRITE (tempLOB, amt, position, buffer);

/* Now you can manipulate the temporary LOB*/

dbms_output.put_line('No of chars in the temporary LOB= ' || DBMS_LOB.GETLENGTH (tempLOB));

dbms_output.put_line('Content = ' || DBMS_LOB.SUBSTR(tempLOB, DBMS_LOB.GETLENGTH (tempLOB), 1)

/*close and free up the temporary LOB

DBMS_LOB.CLOSE (tempLOB);

DBMS_LOB.FREETEMPORARY (tempLOB);

END;

The output of the above code is as follows: -

SQL> /

A temporary LOB has been created
No of chars in the temporary LOB= 14
Content = this is a test

PL/SQL procedure successfully completed.

Restrictions on LOBS: -

There are a few restrictions on how you can use LOBs. In particular,

LOBs cannot be used in the GROUP BY, ORDER BY, SELECT DISTINCT, joins and aggregate function.

They cannot be part of clustered tables.

They are not analysed with the ANALYZE command.

You cannot include them in partitioned index-organised tables.

They are not allowed in VARRAYs.

NCLOBs are not allowed as object attributes.

Conclusion: -

Use LOBs to store large, unstructured data like photo, images, video, documents, etc. They are more efficient that using a LONG or LONG RAW type. You can either store the LOBs within the database (internal LOBs) or store pointers to actual files stored in the operating system (external LOBs). The different types of LOBs include BLOB, CLOB, NCLOB and BFILE.

Oracle provides a number of programmatic interfaces to work with LOBs including PL/SQL, PRO C/C++, OCI etc. We have covered only the PL/SQL interface and the DBMS_LOB package here. For more information about the other interfaces, please check out the Oracle Application Developers’ Guide – Large Objects. You can read it online at technet.oracle.com.

If you have any comments about this document, please let me know at [email protected].

Hosted by www.Geocities.ws