Oracle8 Application Developer's Guide
Release 8.0

A58241-01

Library

Product

Contents

Index

Prev Next

6
Large Objects (LOBs)

Oracle8 provides support for defining and manipulating large objects (LOBs). Oracle8 extends SQL DDL and DML commands to create and update LOB columns in a table or LOB attributes of an object type. Further, Oracle8 provides Oracle Call Interface (OCI) and PL/SQL package APIs to perform random, piecewise operations on LOBs.

This chapter documents the extended SQL commands and the PL/SQL package API for LOBs. It also briefly mentions the OCI API for LOB manipulation, which is described in the Oracle Call Interface Programmer's Guide.

This chapter has two sections:

Introduction to LOBs

Introduction Overview

This section introduces the treatment of LOBs in Oracle8 under the headings that are also laid out below. Although it is not made explicit in the text, the various issues can be grouped under a number of umbrella topics.

The first topic is one of general introduction:

The second topic discusses steps involved in beginning to work with LOBs:

The third topic deals with issues specific to handling external LOBs (BFILEs):

The fourth topic considers how LOBs are handled by way of locators:

The fifth topic is concerned with basic manipulation of LOBs:

Finally, the last topic considers performance and optimization issues in a client/server environment:

What Are LOBs?

Consider the following application scenarios:

Application Scenario 1: :

A law firm wishes to manage production of a significant case by means of a database. The lawyers are aware that the information will include x-rays (image data), expert analysis (character text), depositions (audio/video), and drawings (graphics).During the course of the trial they also come to utilize computer-simulated events (animation).

 

Application Scenario 2:

A broadcast station wishes to manage production of its feature programs by means of a database. The program managers are aware that this information commonly includes photographs (image data), interviews (audio/video), sound-effects (sound waveforms), music (sound waveforms), and script (character text). With the advance of digitizing and storage technology, they also find it possible to include legacy silent-film (video).

 

Application Scenario 3:

A geological survey team looking for oil under the sea wishes to manage its projects by means of a database. The project managers are aware that the information will include satellite pictures (image data) with complex overlay drawings (image data), sonar recordings along with their graphic representations (sound wave forms and image data), and chemical analysis (image data and character text).During the course of the project they also come to employ computer modeling of likely weather conditions (character text and image data).


 

Although each of these scenarios is drawn from a different domain, it is easy to see how management of multiple media is becoming commonplace in business applications. This is relevant to this chapter because Oracle8 supports LOBs - large objects which can hold up to 4 gigabytes of RAW, binary data (e.g., graphic images, sound waveforms, video clips, etc.) or character text data.

Oracle8 regards LOBs as being of two kinds depending on their location with regard to the database - internal LOBs and external LOBs (BFILEs). When the term LOB is used without an identifying prefix term, it refers to both internal and external LOBs. Data stored in a LOB is termed the LOB's value.

Internal LOBs and External LOBs (BFILEs)

Internal LOBs

Internal LOBs, as their name suggests, are stored in the database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. Internal LOBs are also recoverable in the event of transaction or media failure. That is, all the ACID properties that pertain to using database objects pertain to internal LOBs also. This means that any changes to a internal LOB value can be committed or rolled back.

External LOBs (BFILEs)

External LOBs, also referred to as BFILES, are large binary data objects stored in operating system files outside of the database tablespaces. These files use reference semantics. They may be located on hard disks, CDROMs, PhotoCDs or any such device, but a single LOB may not extend from one device to another. The SQL datatype BFILE is supported in Oracle8 SQL and PL/SQL to enable read-only byte stream I/O access to large files existing on the filesystem of the database server. The Oracle Server can access them provided the underlying server operating system supports a stream-mode access to these files.


WARNING:

External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.

 

LOBs in Comparison to LONG and LONG RAW Types

LOBs are similar to LONG and LONG RAW types, but differ in the following ways:

Packages for Working with LOBs

You can make changes to the entire values of internal LOBs through direct SQL DML. You can make to an entire internal LOB, or piecewise to the beginning, middle or end of an internal LOB through the OCI, or through the PL/SQL DBMS_LOB APIs. It is possible to access both internal and external LOBs for read purposes and also write to internal LOBs.

LOB Datatypes

Internal LOB Datatypes

There are three SQL datatypes for defining instances of internal LOBs:

Varying width character data is not supported for BLOBs, CLOBs and NCLOBs.

See Also:

"Working with Varying-Width Character Data" on page 6-58

 

External LOB Datatype

There is one external SQL LOB datatype:

Defining Internal and External LOBs for Tables

It is possible to incorporate LOBs into tables in two ways.

In both cases SQL DDL is used - to define LOB columns in a table and LOB attributes in an object type. Refer to the Oracle8 SQL Reference for information about using LOBs in the following DDL commands:

The following code fragment describes creating the table, lob_table. We refer to this example throughout the text.

    CREATE TABLE lob_table (
      key_value      INTEGER, 
      b_lob          BLOB, 
      c_lob          CLOB, 
      n_lob          NCLOB, 
      f_lob          BFILE);

Stipulating Tablespace and Storage Characteristics for Internal Lobs

When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal LOB. There are no extra tablespace or storage characteristics for external LOBs since they are not stored in the database.

Specifying a name for the LOB data segment and the LOB index makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, DBA_LOBS (see Oracle8 Reference), you see the LOB data segment and LOB index names that you chose instead of system-generated names that are non-intuitive.

The LOB storage characteristics that can be specified for a LOB column or a LOB attribute include PCTVERSION, CACHE, NOCACHE, LOGGING, NOLOGGING, CHUNK and ENABLE/DISABLE STORAGE IN ROW. For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, you should consider the following guidelines.

Tablespace and LOB Index

Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace that is different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column/attribute in order to reduce device contention.

The LOB index is an internal structure that is strongly associated with the LOB storage. This implies that a user may not drop the LOB index and rebuild it. Note that the LOB index cannot be altered through the ALTER INDEX statement although you can alter it through the ALTER TABLE statement. However, you may not rename the LOB index. The system determines which tablespace to use for the LOB data and LOB index depending on the user specification in the LOB storage clause:

Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the table's tablespace. In some extreme cases, it may even be beneficial to use three separate tablespaces - one for the table data, one for the LOB data segments, and one for the LOB index segments. This would be useful if certain LOB data is to be accessed very frequently. Normally, using two tablespaces - one for the table data, and one for the LOB data and LOB index - should be sufficient.

PCTVERSION

When a LOB is modified, a new version of the LOB page is made in order to support consistent read of prior versions of the LOB value.

PCTVERSION is the percent of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space Oracle will try to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning of old LOB data.

Default: 10 (%) Minimum: 0 (%) Maximum: 100 (%)

One way of approximating PCTVERSION is to set PCTVERSION =% of LOBs updated at any given point in time * % of each LOB updated whenever a LOB is updated * % of LOBs being read at any given point in time. Basically, the idea is to allow for a percentage of LOB storage space to be used as old versions of LOB pages so that readers will be able to get consistent reads of data that has been updated.

Example 1:

Several LOB updates concurrent with heavy reads of LOBs.

set PCTVERSION = 20%

Setting PCTVERSION to twice the default allows more free pages to be used for old versions of data pages. Since large queries may require consistent reads of LOBs, it is useful to keep more old versions of LOB pages around. Of course, LOB storage may grow some because Oracle will not be reusing free pages aggressively.

Example 2:

LOBs are created and written just once and are primarily read-only afterwards. Updates are infrequent.

set PCTVERSION = 5% or lower

The more infrequent and smaller the LOB updates are, the less space that needs to be reserved for old copies of LOB data. If existing LOBs are known to be read-only, we could safely set PCTVERSION to 0% since there would never be any pages needed for old versions of data.

CACHE / NOCACHE

Use the CACHE option on LOBs if the same LOB data will be accessed frequently. Use the NOCACHE option (the default) if LOB data will be read only once, or infrequently.

LOGGING / NOLOGGING

[NO] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [NO]LOGGING clause is omitted, this means that neither NO LOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.

For LOBs, there is a further alternative depending on how CACHE is stipulated.

The following issues should also be kept in mind.

CHUNK

Set CHUNK to the number of blocks of LOB data that will be accessed at one time i.e. the number of blocks that will be read/written via OCILobRead(), OCILobWrite(), DBMS_LOB.READ(), or DBMS_LOB.WRITE() during one access of the LOB value. For example, if only 1 block of LOB data is accessed at a time, set CHUNK to the size of one block. For example, if the database block size is 2K, then set CHUNK to 2K.

If you explicitly specify the storage characteristics for the LOB, make sure that INITIAL and NEXT for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK of 8K, make sure that the INITIAL and NEXT are bigger than 8K and preferably considerably bigger (for example, at least 16K).

Put another way: If you specify a value for INITIAL, NEXT or the LOB CHUNK size, make sure that:

and

ENABLE | DISABLE STORAGE IN ROW

You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (i.e. in the row) or out of line. You may not alter this specification once you have made it: if you ENABLE STORAGE IN ROW, you cannot alter it to DISABLE STORAGE IN ROW and vice versa. The default is ENABLE STORAGE IN ROW.

The maximum amount of LOB data that will be stored in the row is the maximum VARCHAR size (4000). Note that this includes the control information as well as the LOB value. If the user indicates that the LOB should be stored in the row, once the LOB value and control information is larger than 4000, the LOB value is automatically moved out of the row.

This suggests the following guideline. If the LOB is small (i.e. < 4000 bytes), then storing the LOB data out of line will decrease performance. However, storing the LOB in the row increases the size of the row. This will impact performance if the user is doing a lot of base table processing, such as full table scans, multi-row accesses (range scans) or many UPDATE/SELECT to columns other than the LOB columns. If the user doesn't expect the LOB data to be < 4000, i.e. if all LOBs are big, then the default is the best choice since

(a) the LOB data is automatically moved out of line once it gets bigger than 4000 (which will be the case here since the LOB data is big to begin with), and

(b) performance will be slightly better since we still store some control information in the row even after we move the LOB data out of the row.

Initializing Internal LOBs (SQL DML)

You can set an internal LOB - -that is, a LOB column in a table, or a LOB attribute in an object type defined by you- to be empty, or NULL. An empty LOB stored in a table is a LOB of zero length that has a locator. If you SELECT from an empty LOB column / attribute, you get back a locator which you can use to populate the LOB with data via the OCI or DBMS_LOB routines. This is discussed in more detail below.

Alternatively, LOB columns, but not LOB attributes, may be initialized to a value. Which is to say - internal LOB attributes differ from internal LOB columns in that LOB attributes may not be initialized to a value other than NULL or empty. As discussed below, an external LOB (i.e. BFILE) can be initialized to NULL or to a filename.

For example, let us say that you create the table, lob_table:

    CREATE TABLE lob_table (
      key_value      INTEGER, 
      b_lob          BLOB, 
      c_lob          CLOB, 
      n_lob          NCLOB, 
      f_lob          BFILE);

You can initialize the LOBs by using the following SQL INSERT statement:

INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), NULL,
    EMPTY_CLOB(), NULL);

This sets the value of b_lob and n_lob to an empty value, and sets c_lob and f_lob to NULL.

Setting the LOB to NULL

You may want to set the internal LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT and/or if you want to issue a SELECT statement thereafter such as:

     SELECT * FROM a_table WHERE a_lob_col != NULL; 

or

     SELECT * FROM a_table WHERE a_lob_col == NULL; 

However, the drawback to this approach is that you must then issue a SQL UPDATE statement to set the NULL LOB column to EMPTY_BLOB() /EMPTY_CLOB() or to a value (e.g. 'abc') for internal LOBs or to a filename for external LOBs. You cannot call the OCI or the PL/SQL DBMS_LOB functions on a NULL LOB. These functions only work with a locator and if the LOB column is NULL, there is no locator in the row.

Setting the internal LOB to empty

The other option is for you to set the LOB value to empty by using the function EMPTY_BLOB () /EMPTY_CLOB() in the INSERT statement:

     INSERT INTO a_table values (empty_blob());

Even better is to use the RETURNING clause (thereby eliminating a round trip that is necessary for the subsequent SELECT), and then immediately call OCI or the PL/SQL DBMS_LOB functions to populate the LOB with data.

See Also:

"EMPTY_BLOB() and EMPTY_CLOB() Functions" on page 6-60

 

Accessing External LOBs (SQL DML)

Directory Object

The DIRECTORY object enables administering the access and usage of BFILEs in an Oracle8 Server (see the CREATE DIRECTORY command in the Oracle8 Reference). A DIRECTORY specifies a logical alias name for a physical directory on the server's filesystem under which the file to be accessed is located. You can access a file in the server's filesystem only if granted the required access privilege on the DIRECTORY object.

The DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hardcode the absolute pathnames of the physical files in your applications. A DIRECTORY alias is used in conjunction with the BFILENAME() function (in SQL and PL/SQL), or the OCILobFileSetName() (in OCI) for initializing a BFILE locator.


WARNING:

Oracle does not verify that the directory and pathname you specify actually exist. You should take care to specify a valid directory in your operating system. If your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format. There is no need to specify a terminating slash (e.g., /tmp/ is not necessary, simply use /tmp).

 

Initializing BFILES using BFILENAME()

In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object which is an alias for the full pathname to the operating system file.

See Also:

"Directory Object" on page 6-15.

 

You use Oracle8 SQL DML to associate existing operating system files with the relevant database records of a particular table. You can use the SQL INSERT statement to initialize a BFILE column to point to an existing file in the server's filesystem, and you can use a SQL UPDATE statement to change the reference target of the BFILE. You can also initialize a BFILE to NULL and then update it later to refer to an operating system file via the BFILENAME() function. OCI users can also use OCILobFIleSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.

For example, the following statements associate the files image1.gif and image2.gif with records having key_value of 21 and 22 respectively. 'IMG' is a DIRECTORY object that represents the physical directory under which image1.dif and image2.dif are stored.

   INSERT INTO lob_table VALUES
         (21, NULL, NULL, NULL, BFILENAME('IMG', 'image1.gif'));
    INSERT INTO lob_table VALUES
         (22, NULL, NULL, NULL, BFILENAME('IMG', 'image2.gif'));

The UPDATE statement below changes the target file to image3.gif for the row with key_value 22.

   UPDATE lob_table SET f_lob = BFILENAME('IMG', 'image3.gif') 
        WHERE key_value = 22;

BFILENAME() is a built-in function that is used to initialize the BFILE column to point to the external file.

See Also:

"BFILENAME() Function" on page 6-62.

 

Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using PL/SQL DBMS_LOB package and OCI. However, these files are read-only when accessed through BFILES, and so they cannot be updated or deleted through BFILES.

As a consequence of the reference-based semantics for BFILEs, it is possible to have multiple BFILE columns in the same record or different records referring to the same file. For example, the UPDATE statements below set the BFILE column of the row with key_value 21 in lob_table to point to the same file as the row with key_value 22.

UPDATE lob_table 
   SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22)     
   WHERE key_value = 21;

DIRECTORY Name Specification

The naming convention followed by Oracle8 for DIRECTORY objects is the same as that done for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement

CREATE DIRECTORY scott_dir AS '/usr/home/scott';

creates a directory object whose name is 'SCOTT_DIR' (in uppercase). But if a delimited identifier is used for the DIRECTORY name, as shown in the following statement

CREATE DIRECTORY "Mary_Dir" AS '/usr/home/mary';

the directory object's name is 'Mary_Dir'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:

BFILENAME('SCOTT_DIR', 'afile')
BFILENAME('Mary_Dir', 'afile')

BFILE Security

This section introduces the BFILE security model and the associated SQL DDL and DML. The main features for BFILE security in Oracle 8.0 are:

Ownership and Privileges

The DIRECTORY is a system owned object. For more information on system owned objects, see Oracle8 SQL Reference. Oracle8 supports two new system privileges, which are granted only to the DBA account:

The READ privilege on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege. If you have been granted the READ privilege with GRANT option, you may in turn grant this privilege to other users/roles and add them to your privilege domains.

It is important to note that the READ privilege is defined only on the DIRECTORY object. The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process. It is the DBA's responsibility to ensure that the physical directory exists, and read permission for the Oracle Server process is enabled on the file, the directory, and the path leading to it. It is also the DBA's responsibility to make sure that the directory remains available, and the read permission remains enabled, for the entire duration of file access by database users.

The privilege just implies that as far as the Oracle8 Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.


WARNING:

Since the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges potentially expose the server filesystem to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent any accidental or malicious security breach.

 

SQL DDL for BFILE security

Refer to the Oracle8 SQL Reference for information about the following SQL DDL commands that create, replace, and drop directory objects:

SQL DML for BFILE security

Refer to the Oracle8 SQL Reference for information about the following SQL DML commands that provide security for BFILEs:

Catalog Views on Directories

Catalog views are provided for directory objects to enable users to view object names and their corresponding paths and privileges. The supported views are:

Guidelines for DIRECTORY Usage

The main goal of the DIRECTORY feature in Oracle8 is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server filesystem. But to realize this goal, it is very important that the DBA follow these guidelines when using directory objects:

In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have read privileges for the Oracle process, DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.

Maximum Number of Open BFILEs

A limited number of BFILEs can be open simultaneously per session. The maximum number is specified by a new initialization parameter, the SESSION_MAX_OPEN_FILES parameter.

SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session. The default value for this parameter is 10. That is, a maximum of 10 files can be opened simultaneously per session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

BFILEs in MTS Mode

Oracle8 release 8.0 does not support session migration for BFILEs in MTS mode. This implies that operations on open BFILEs can persist beyond the end of a call to an MTS server. Sessions involving BFILE operations need to be bound to one shared server, they cannot migrate from one server to another.

Closing BFILEs after Program Termination

It is the user's responsibility to close any opened file(s) after normal or abnormal termination of a PL/SQL program block or OCI program. So, for instance, for every DBMS_LOB FILEOPEN call, there must be a matching DBMS_LOB FILECLOSE call. You should close open files before the termination of a PL/SQL block or OCI program, and also in situations which have raised errors. The exception handler should make provision to close any files that were opened before the occurrence of the exception or abnormal termination.

If this is not done, Oracle will consider these files unclosed, and if the number of unclosed files exceeds the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the FILECLOSEALL call.

See Also:

"DBMS_LOB General Usage Notes" on page 6-71 for more details on PL/SQL programming

 

LOB Value and Locators

Inline storage of the LOB value

Data stored in a LOB is termed the LOB's value. The value of an internal LOB may or may not be stored inline with the other row data. If the internal LOB value is less than approximately 4000 bytes, then the value is stored inline; otherwise it is stored outside the row. Since LOBs are intended to be large objects, inline storage will only be relevant if your application mixes 'small' and 'large' LOBs.

As mentioned above ("ENABLE | DISABLE STORAGE IN ROW" on page 6-12), the LOB value is automatically moved out of the row once it extends beyond approximately 4000 bytes.

LOB locators

Regardless of where the value of the internal LOB is stored, a locator is stored in the row. You can think of a LOB locator as a pointer to the actual location of the LOB value. A LOB locator is a locator to an internal LOB while a BFILE locator is a locator to an external LOB. When the term locator is used without an identifying prefix term, it refers to both LOB locators and BFILE locators.

Internal LOB Locators

For internal LOBs, the LOB column stores a locator to the LOB's value which is stored in a database tablespace. Each LOB column/attribute for a given row has its own distinct LOB locator and copy of the LOB value stored in the database tablespace.

External LOB Locators (BFILE Locators)

For BFILEs, the value is stored in a server-side operating system file, i.e. external to the database. The BFILE locator that refers to that file is stored in the row. If a BFILE locator variable that is used in a DBMS_LOB FILEOPEN() (for example L1) is assigned to another locator variable, (for example L2), both L1 and L2 point to the same file. This means that two rows in a table with a BFILE column can refer to the same file or to two distinct files - a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.

A BFILE locator variable in a PL/SQL or OCI program behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O library of most conventional programming languages. This implies that once you define and initialize a BFILE locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.

The BFILE locator variable can be used, just as any scalar, as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level, in PL/SQL and OCI programs.

LOB Locator Operations

Setting the LOB Column/Attribute to contain a locator

Before you can start writing data to a internal LOB, the LOB column/attribute must be made non-null, that is, it must contain a locator. Similarly, before you can start accessing the BFILE value, the BFILE column/attribute must be made non-null.

Invoking the EMPTY_BLOB() or EMPTY_CLOB() function in and of itself does not raise an exception. However, using a LOB locator that was set to empty to access or manipulate the LOB value in any PL/SQL DBMS_LOB or OCI routine will raise an exception. Valid places where empty LOB locators may be used include the VALUES clause of an INSERT statement and the SET clause of an UPDATE statement.

The following INSERT statement

INSERT INTO lob_table VALUES (1002, NULL 'abcde', 
    NULL,BFILENAME('SCOTT_DIR', 'scott.dat'));

Similarly, given a table person_objcol_table one of whose columns is an object with LOB attributes, the LOB attributes can be initialized to NULL or set to empty as shown below:

INSERT INTO person_objcol_table VALUES (1001,person_type
    ('Scott', EMPTY_CLOB(), EMPTY_BLOB(),
    BFILENAME('SCOTT_DIR', 'scott.dat')));

Accessing a LOB through a locator

SELECTing a LOB

Performing a SELECT on a LOB returns the locator instead of the LOB value. In the following PL/SQL fragment you select the LOB locator for b_lob and place it in the PL/SQL locator variable image1 defined in the program block. When you use PL/SQL DBMS_LOB functions to manipulate the LOB value, you refer to the LOB using the locator.


DECLARE
    image1       BLOB;
    image_no     INTEGER := 101;
BEGIN
    SELECT b_lob INTO image1 FROM lob_table
        WHERE key_value = image_no;
    DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
        DBMS_LOB.GETLENGTH(image1));
    -- more LOB routines
END;

In using OCI, locators are mapped to locator pointers which are used to manipulate the LOB value. As mentioned before, the OCI LOB interface is described briefly in "Using the OCI to Manipulate LOBs" on page 6-64, and more extensively in the Oracle Call Interface Programmer's Guide.

Locking an Internal LOB before Updating

Prior to updating a LOB value via the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

Read consistent locators

Oracle provides the same read consistency mechanisms for LOBs as for all other database reads and updates (refer to Oracle8 Concepts for general information about read consistency). However, read consistency has some special applications to LOB locators that need to be clearly understood.

A SELECTed locator, regardless of the existence of the FOR UPDATE clause, becomes a read consistent locator, and remains a read consistent locator until the LOB value is updated through that locator. A read consistent locator contains the snapshot environment as of the point in time of the SELECT.

This has some complex implications. Let us say that you have created a read consistent locator (L1) by way of a SELECT operation. In reading the value of the internal LOB through L1, the LOB is read as of the point in time of the SELECT statement even if the SELECT statement includes a FOR UPDATE. Further, if the LOB value is updated through a different locator (L2) in the same transaction, L1 does not see L2's updates. In addition, L1 will not see committed updates made to the LOB through another transaction.

Furthermore, if the read consistent locator L1 is copied to another locator L2 (for example, by a PL/SQL assignment of two locator variables - L2:= L1), then L2 becomes a read consistent locator along with L1 and any data read is read as of the point in time of the SELECT for L1.

Clearly you can utilize the existence of multiple locators to access different transformations of the LOB value. However, in taking this course, you must be careful to keep track of the different values accessed by different locators. The following code demonstrates the relationship between read-consistency and updating in a simple example.

Using lob_table as defined above and PL/SQL, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.

Example of a Read Consistent Locator
INSERT INTO lob_table 
   VALUES (1, NULL, 'abcd', NULL, NULL); 

COMMIT; 

DECLARE 
  num_var           INTEGER; 
  clob_selected     CLOB; 
  clob_updated      CLOB; 
  clob_copied       CLOB; 
  read_amount       INTEGER; 
  read_offset       INTEGER; 
  write_amount      INTEGER; 
  write_offset      INTEGER; 
  buffer            VARCHAR2(20); 
 
BEGIN
  -- At time t1: 
  SELECT c_lob INTO clob_selected 
     FROM lob_table 
     WHERE key_value = 1; 

  -- At time t2: 
  SELECT c_lob INTO clob_updated 
     FROM lob_table 
     WHERE key_value = 1 
     FOR UPDATE; 
 
  -- At time t3: 
  clob_copied := clob_selected; 
  -- After the assignment, both the clob_copied and the 
  -- clob_selected have the same snapshot as of the point in time
  -- of the SELECT into clob_selected 

  -- Reading from the clob_selected and the clob_copied will  
  -- return the same LOB value. clob_updated also sees the same    
  -- LOB value as of its select:
  read_amount := 10; 
  read_offset := 1;  
  dbms_lob.read(clob_selected, read_amount, read_offset, 
       buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
  -- Produces the output 'abcd'
  
  read_amount := 10; 
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcd'
  
  read_amount := 10; 
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcd'
  
  -- At time t4: 
  write_amount := 3; 
  write_offset := 5; 
  buffer := 'efg'; 
  dbms_lob.write(clob_updated, write_amount, write_offset,
       buffer);
  
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcdefg'
  
  -- At time t5: 
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset, 
       buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
  -- Produces the output 'abcd'
  
  -- At time t6: 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcd'
END; 
/

Updated locators

When you update the value of the internal LOB through the LOB locator (L1), L1 (that is, the locator itself) is updated to contain the current snapshot environment as of the point in time after the operation was completed on the LOB value through the locator L1. L1 is then termed an updated locator. This operation allows you to see your own changes to the LOB value on the next read through the same locator, L1.


Note:

the snapshot environment in the locator is not updated if the locator is used to merely read the LOB value. It is only updated when you modify the LOB value through the locator via the PL/SQL DBMS_LOB package or the OCI LOB APIs.

 

Any committed updates made by a different transaction are seen by L1 only if your transaction is a read-committed transaction and if you use L1 to update the LOB value after the other transaction committed.


Note:

When you update an internal LOB's value, the modification is always made to the most current LOB value.

 

Updating the value of the internal LOB through the OCI LOB APIs or the PL/SQL DBMS_LOB package can be thought of as updating the LOB value and then reselecting the locator that refers to the new LOB value.

Note that updating the LOB value through SQL is merely an UPDATE statement. It is up to you to do the reselect of the LOB locator or use the RETURNING clause in the UPDATE statement (see the PL/SQL User's Guide and Reference) so that the locator can see the changes made by the UPDATE statement. Unless you reselect the LOB locator or use the RETURNING clause, you may think you are reading the latest value when this is not the case. For this reason you should avoid mixing SQL DML with OCI and DBMS_LOB piecewise operations.

Using lob_table as defined above, a CLOB locator is created: clob_selected.

Example of Repercussions of Mixing SQL DML with DMBS_LOB
INSERT INTO lob_table VALUES (1, NULL, 'abcd', NULL, NULL); 
COMMIT; 
 
DECLARE 
  num_var           INTEGER; 
  clob_selected     CLOB; 
  read_amount       INTEGER; 
  read_offset       INTEGER; 
  buffer            VARCHAR2(20); 

BEGIN
 
  -- At time t1: 
  SELECT c_lob INTO clob_selected 
  FROM lob_table 
  WHERE key_value = 1;
  
  read_amount := 10; 
  read_offset := 1; 
  dbms_lob.read(clob_selected, read_amount, read_offset, 
       buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
  -- Produces the output 'abcd'
  
  -- At time t2: 
  UPDATE lob_table SET c_lob = empty_clob() 
      WHERE key_value = 1; 
  -- although the most current current LOB value is now empty, 
  -- clob_selected still sees the LOB value as of the point
  -- in time of the SELECT
  
  read_amount := 10; 
  dbms_lob.read(clob_selected, read_amount, read_offset,
     buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
  -- Produces the output 'abcd'
  
  -- At time t3: 
  SELECT c_lob INTO clob_selected FROM lob_table WHERE
       key_value = 1; 
  -- the SELECT allows clob_selected to see the most current
  -- LOB value
  
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer); 
  -- ERROR: ORA-01403: no data found
END; 
/   


WARNING:

we advise that you avoid updating the same LOB with different locators. You will avoid many pitfalls if you use only one locator to update the same LOB value.

 

Using lob_table as defined above, two CLOBs are created as potential locators: clob_updated and clob_copied.

Example of an Updated LOB Locator
INSERT INTO lob_table 
   VALUES (1, NULL, 'abcd', NULL, NULL); 

COMMIT; 
 
DECLARE 
  num_var          INTEGER; 
  clob_updated     CLOB; 
  clob_copied      CLOB; 
  read_amount      INTEGER; ;
  read_offset      INTEGER; 
  write_amount     INTEGER; 
  write_offset     INTEGER; 
  buffer           VARCHAR2(20); 
BEGIN 
  
-- At time t1:
  SELECT c_lob INTO clob_updated FROM lob_table 
      WHERE key_value = 1 
      FOR UPDATE; 
  
  -- At time t2:
  clob_copied := clob_updated;
  -- after the assign, clob_copied and clob_updated see the same
  -- LOB value
  
  read_amount := 10; 
  read_offset := 1; 
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcd'
  
  read_amount := 10; 
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcd'
  

  -- At time t3:
  write_amount := 3; 
  write_offset := 5; 
  buffer := 'efg'; 
  dbms_lob.write(clob_updated, write_amount, write_offset,
        buffer); 
  
  read_amount := 10; 
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcdefg'
  

  -- At time t4:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcd'
  

  -- At time t5:
  clob_copied := clob_updated;
  
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcdefg'
END; 
/

LOB bind variables

When a LOB locator is used as the source to update another internal LOB (as in a SQL INSERT or UPDATE statement, the DBMS_LOB.COPY routine, and so on), the snapshot environment in the source LOB locator determines the LOB value that is used as the source. If the source locator (for example L1) is a read consistent locator, then the LOB value as of the point in time of the SELECT of L1 is used. If the source locator (for example L2) is an updated locator, then the LOB value associated with L2's snapshot environment at the time of the operation is used.

Using lob_table as defined above, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.

Example of Updating a LOB with a PL/SQL Variable
INSERT INTO lob_table 
   VALUES (1, NULL, 'abcd', NULL, NULL); 

COMMIT; 
 
DECLARE 
  num_var           INTEGER; 
  clob_selected     CLOB; 
  clob_updated      CLOB; 
  clob_copied       CLOB; 
  read_amount       INTEGER; 
  read_offset       INTEGER; 
  write_amount      INTEGER; 
  write_offset      INTEGER; 
  buffer            VARCHAR2(20);
BEGIN

  -- At time t1:
  SELECT c_lob INTO clob_updated FROM lob_table 
      WHERE key_value = 1 
      FOR UPDATE;
  
  read_amount := 10; 
  read_offset := 1;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcd'
  
 
  -- At time t2:
  clob_copied := clob_updated;
  

  -- At time t3:
  write_amount := 3; 
  write_offset := 5; 
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset, 
       buffer);
  
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_updated value: ' || buffer); 
  -- Produces the output 'abcdefg'
  -- note that clob_copied doesn't see the write made before   
  -- clob_updated
  

  -- At time t4:
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer); 
  dbms_output.put_line('clob_copied value: ' || buffer); 
  -- Produces the output 'abcd'

  -- At time t5:
  -- the insert uses clob_copied view of the LOB value which does 
  -- not include clob_updated changes
  INSERT INTO lob_table values (2, NULL, clob_copied, NULL,
      NULL) RETURNING c_lob INTO clob_selected; 
    
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset,
       buffer); 
  dbms_output.put_line('clob_selected value: ' || buffer); 
  -- Produces the output 'abcd'
END; 
/   

LOB locators cannot span transactions

Modifying an internal LOB's value through the LOB locator via DBMS_LOB, OCI, or SQL INSERT or UPDATE statements changes the locator from a read consistent locator to an updated locator. Further, the INSERT or UPDATE statement automatically starts a transaction and locks the row. Once this has occurred, the locator may not be used outside the current transaction. In other words, LOB locators cannot span transactions.

Using lob_table as defined above, a CLOB locator is created: clob_updated.

Example of Locator Not Spanning a Transaction
INSERT INTO lob_table 
     VALUES (1, NULL, 'abcd', NULL, NULL); 
COMMIT;

DECLARE 
  num_var          INTEGER; 
  clob_updated     CLOB; 
  read_amount      INTEGER; 
  read_offset      INTEGER; 
  write_amount     INTEGER; 
  write_offset     INTEGER; 
  buffer           VARCHAR2(20);

BEGIN
     
     -- At time t1:
     SELECT      c_lob 
     INTO        clob_updated 
     FROM        lob_table 
     WHERE       key_value = 1 
     FOR UPDATE;
     
     read_amount := 10; 
     read_offset := 1;
     dbms_lob.read(clob_updated, read_amount, read_offset, 
          buffer); 
     dbms_output.put_line('clob_updated value: ' || buffer);
     -- This produces the output 'abcd'
   
     -- At time t2:
     write_amount := 3; 
     write_offset := 5; 
     buffer := 'efg';
     dbms_lob.write(clob_updated, write_amount, write_offset,   
          buffer);
    
     read_amount := 10;
     dbms_lob.read(clob_updated, read_amount, read_offset, 
         buffer); 
     dbms_output.put_line('clob_updated value: ' || buffer); 
     -- This produces the output 'abcdefg'
    
 
    -- At time t3:
    COMMIT;
    
 
    -- At time t4:
    read_amount := 10;
    dbms_lob.read(clob_updated , read_amount, read_offset,
         buffer); 
    -- ERROR: ORA-22990: LOB locators cannot span transactions
END; 
/

Examples of a Locator Not Spanning a Transaction

Assume the following tables:

CREATE TABLE tdrslob01 (
     a    CLOB,
     b    BLOB,
     c    NUMBER);

CREATE TABLE foo (
     key  NUMBER);  

CONNECT to the database 
EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" 
[ARRAY FETCH GET 16 rows INTO OCILobLocator ARRAY] 
for (i=0; i<16; i++) 
{ 
  EXECUTE "INSERT INTO foo VALUES(5)" 
  OCITransCommit(...); 
  FETCH TEXT in CLOB USING locators fetched 
} 

The sequence runs successfully because the SELECT of the locators occurs outside of a transaction. This means that the locators selected are not associated with a transaction. Even though the INSERT in the 'for' loop implicitly starts a transaction, the subsequent COMMIT in the 'for' loop ends the transaction. The FETCH of the LOB data via the locator returned from the SELECT outside a transaction succeeds. Both the SELECT of the locator and the FETCH of the locator data occur outside a transaction.

However, the addition of one statement produces an error:

CONNECT to the database 
EXECUTE "INSERT INTO foo VALUES(5)"  <===  
EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" 
[GET 16 rows] 
for (i=0; i<16; i++) 
{ 
  EXECUTE "INSERT INTO foo VALUES(5)" 
  OCITransCommit(...); 
  FETCH text in CLOB USING locators fetched <== get ORA-22990 
} 

In the second example, the SELECT of the locators occurs inside a transaction (the INSERT statement implicitly started a transaction). This means that the locators selected are associated with a transaction. The COMMIT in the 'for' loop commits the transaction in which the locators were selected. Therefore, the subsequent FETCH is trying to fetch locator values from the previous transaction which was already committed. Consequently, the 22990 error is returned.

Executing a COMMIT right after the first INSERT will succeed:

CONNECT to the database 
EXECUTE "INSERT INTO foo VALUES(5)"  <===  
OCITransCommit(...);  <=== 
EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" 
[get 16 rows] 
for (i=0; i<16; i++) 
{ 
  EXECUTE "INSERT INTO foo VALUES(5)" 
  OCITransCommit(...); 
  FETCH text in CLOB using locators fetched 
} 

In this example, the INSERT implicitly starts a transaction and the COMMIT ends the transaction. Therefore, the SELECT of the locators occurs outside of a transaction. This means that the locators selected are not associated with a transaction. Again, even though the INSERT in the 'for' loop implicitly starts a transaction, the subsequent COMMIT in the 'for' loop ends the transaction. Therefore, the FETCH of the LOB data via the locator returned from the SELECT which occurred outside a transaction succeeds. Both the SELECT of the locator and the FETCH of the locator data occur outside of a transaction.

Efficient Reads and Writes of Large Amounts of LOB Data

The most efficient way to read or write large amounts of LOB data is to use OCILobRead() or OCILobWrite() with the streaming mechanism enabled via polling or a callback.

See Also:

Oracle Call Interface Programmer's Guide for more information about these APIs and a sample program of how to use them.

 

Reading LOB Values

When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.

For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization all parameters:

#define MAX_LOB_SIZE 4294967295 
ub4  amount =  MAX_LOB_SIZE; 
ub4  offset = 1000; 
OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0) 

Writing LOB Values

As noted previously, the best way to populate the LOB with data, or write large amounts of data to the LOB, is to use the OCILobWrite() call with streaming. If you know how much data will be written to the LOB, specify that amount when calling OCILobWrite(). This will allow for the contiguity of the LOB data on disk. Apart from being spatially efficient, contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.

Copying LOBs

Copying internal LOBs

The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as opposed to the reference semantics which apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another row in the same table or in a different table, the actual LOB value is copied, not just the LOB locator. For example, assuming lob_table1 and lob_table2 have schemas identical to lob_table described above, the statement

INSERT INTO lob_table1 (key_value, b_lob)
    (SELECT key_value, b_lob FROM lob_table2 T2
     WHERE T2.key_value = 101);

creates a new LOB locator in the table lob_table1, and copies the LOB data from lob_table2 to the location pointed to by a new LOB locator which is inserted into table lob_table1.

Copying external LOBs

BFILE types use reference semantics instead of copy semantics. This means that only the BFILE locator is copied from one row to another row. Put another way: it is not possible to make a copy of an external LOB value without issuing an operating system command to copy the operating system file.

Deleting LOBs

Deleting Internal LOBs

You delete a row that contains an internal LOB column / attribute by (a) using the explicit SQL DML command DELETE, or (b) using a SQL DDL command that effectively deletes it, such as DROP TABLE, TRUNCATE TABLE, or DROP TABLESPACE. In either case you delete the LOB locator and the LOB value as well.

But note that due to the consistent read mechanism, the old LOB value remains accessible with the value that it had at the time of execution of the statement (such as SELECT) that returned the LOB locator.

See Also:

"Read consistent locators" on page 6-24.

 

Of course, two distinct rows of a table with a LOB column have their own distinct LOB locators and distinct copies of the LOB values irrespective of whether the LOB values are the same or different. This means that deleting one row has no effect on the data or LOB locator in another row even if one LOB was originally copied from another row.

Deleting External LOBs

The LOB value in a BFILE, however, does not get deleted by using SQL DDL or SQL DML commands. Only the BFILE locator is deleted. Deletion of a record containing a BFILE column amounts to de-linking that record from an existing file, not deleting the physical operating system file itself. An SQL DELETE statement on a particular row deletes the BFILE locator for the particular row, thereby removing the reference to the operating system file.

The following DELETE, DROP TABLE, or TRUNCATE TABLE statements delete the row, and hence the BFILE locator that refers to image1.gif, but leave the operating system file undeleted in the filesystem.

DELETE FROM lob_table 
     WHERE key_value = 21;

DROP TABLE lob_table;
 
TRUNCATE TABLE lob_table; 

Copying Data from LONGs to LOBs

One of the problems you may face is how to convert data from the LONG datatype into LOB format. The loadlob.sql PL/SQL program demonstrates how to convert a LONG to a LOB by using the DBMS_LOB.LOADFROMFILE method. The program requires that you perform a sequence of steps:

  1. Leave the LONG column in the old table and add a new LOB column using the ALTER TABLE command.


    Note:

    The ALTER TABLE command is not able to change the type of a LONG column to a LOB column. LONG and LOB columns are two distinct datatypes so it is not possible to assign a LONG column to a LOB column.

     

  2. Write the data in the LONG or LONG RAW to a flat file.
  3. Use CREATE DIRECTORY to point to the directory where the BFILE (flat file) was written.
  4. Using either OCI or PL/SQL, there are three different ways you can copy the data from the server-side flat file into the LOB:
    1. The OCI command OCILobLoadFromFile or the PL/SQL command DBMS_LOB.LOADFROMFILE(): This is the fastest ways to copy from a server-side operating system flat file to a LOB.
    2. The OCI command OCILobWrite() from a server-side external procedure: The flat file will be on the server-side even if the program which calls the server-side external procedure is run from the client. This is the second fastest way to transfer from a server side operating system flat file to a LOB.
    3. The OCI command OCILobWrite(): This method is used in the bull_lob program listed below. This may not be the fastest way to convert a LONG to a LOB, but it may be the only alternative in the circumstances. In such cases, when the program is run on a remote client machine, the LONG data on the server must be written to a client machine flat file, and then the client flat file written back to the server LOB column. Since this will involve two trips across the network, the load on performance must be considered if this will be an operation that is frequently repeated.


      Note:

      The user will need to do their own character set conversions for CLOBS and NCLOBS because the flat file or BFILE will store the data as binary or raw data.

       


      WARNING:

      The export/import utility is currently not capable of converting from LONGS TO LOBs.

       

Example

The example that follows shows the PL/SQL version of method 4(a) listed above for loading a LONG which has been written to a flat file named /tmp/sound_clip into a LOB column.


Note:

There is a separate bulletin which addresses how to convert a LONG to a LOB using the OCILobWrite command.

 

Complete the following steps to execute the loadlob.sql PL/SQL script:

  1. Create the file sound_clip with the following contents and copy it to the /tmp directory:
    sound_clip: abcdefghijklmnopqrstuvwxyz 
    

  1. Run the following SQL script:

% sqlplus scott/tiger @loadlob 
loadlob.sql 
-----------
set echo on; 
connect sys/change_on_install;  
grant all on dbms_lob to scott;  
grant create any directory to scott;  
connect scott/tiger; 
drop directory some_dir_alias; 
create directory some_dir_alias as '/tmp';  
drop table multimedia; 
  
/* Create the table */  
  
CREATE TABLE multimedia  
(  
   id           NUMBER,  
   video_clip   CLOB DEFAULT empty_clob(),  
   audio_clip   CLOB DEFAULT NULL,  
   some_file    BFILE DEFAULT NULL  
) ;  
  
/* Load data into the table */  
/* Insert 10 rows into the table which defaults to initializing */   
/* the video_clip to empty and the audio_clip and some_file to null. */  
  
/* The fastest way to do this is to use array inserts with OCI */   
/* (see OCIBindArrayOfStruct) */  
/* The less speedy method is to use a loop in PL/SQL as follows. */   
  
declare  
   loop_count integer;  
begin  
   loop_count := 1;  
   while loop_count <= 10 loop  
     insert into multimedia (id) values (loop_count);  
     loop_count := loop_count + 1;  
   end loop;  
end;  
/  
  
/* Initialize the first audio clip to the actual value. */   
/* Then copy this value to all rows in the table. */  

declare  
  ac      clob;  
  amount  integer;  
  a_file  bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip');  
begin  
  update multimedia set audio_clip = empty_clob() where id = 1 returning  
        audio_clip into ac;  
  
/* Open the server side file that contains the audio clip, load it into */  
/* the CLOB and then close the file.  Assume that the audio clip is */  
/* only 32,000 bytes long and that it starts at position 1 in the file. */  
 
  dbms_lob.fileopen(a_file, dbms_lob.file_readonly);  
  amount := 26; 
 
/* Note that the destination and source offsets default to 1 */
  
  dbms_lob.loadfromfile(ac, a_file, amount);  
  dbms_lob.fileclose(a_file);  
  commit;  
  
/* Update all rows in the table to the audio clip you just loaded. */  
  update multimedia set audio_clip =   
        (select audio_clip from multimedia where id = 1)  
        where audio_clip is null;  
end;  
/  
 
select id, audio_clip from multimedia; 

  1. The output should resemble:
    SQL> @loadlob 
    SQL> set echo on; 
    SQL> connect sys/change_on_install; 
    Connected. 
    SQL> GRANT ALL on dbms_lob to scott; 
    Grant succeeded. 
    SQL> GRANT CREATE ANY DIRECTORY to scott; 
    Grant succeeded. 
    SQL> CONNECT scott/tiger; 
    Connected. 
    SQL> DROP DIRECTORY some_dir_alias; 
    Directory dropped. 
    SQL> CREATE DIRECTORY some_dir_alias as '/tmp'; 
    Directory created. 
    SQL> DROP TABLE multimedia; 
    Table dropped. 
    SQL>  
    SQL> /* CREATE THE TABLE */ 
    SQL>  
    SQL> create table multimedia 
      2  ( 
      3  	id	     number, 
      4  	video_clip   clob default empty_clob(), 
      5  	audio_clip   clob default null, 
      6  	some_file    bfile default null 
      7  ) ; 
     
    Table created. 
     
    SQL>  
    SQL>  
    SQL> /* LOAD DATA INTO THE TABLE */ 
    SQL> /* Insert 10 rows into the table which defaults to initializing */
    DOC> /* the video_clip to empty and the audio_clip and some_file to null.*/ 
    DOC> */ 
    SQL>  
    SQL> /* The fast way to do this is to use array inserts with OCI */
    DOC> /* (see OCIBindArrayOfStruct) */
    DOC> /* The not so fast way is to use a loop in plsql as follows. */
    
    SQL>  
    SQL> declare 
      2  	loop_count integer; 
      3  begin 
      4  	loop_count := 1; 
      5  	while loop_count <= 10 loop 
      6  	  insert into multimedia (id) values (loop_count); 
      7  	  loop_count := loop_count + 1; 
      8  	end loop; 
      9  end; 
     10  / 
     
    PL/SQL procedure successfully completed. 
     
    SQL> /* Initialize the first audio clip to the actual value. */
    DOC> /* Then copy this value to all rows in the table. */
    
    SQL> DECLARE 
      2    ac      CLOB; 
      3    amount  INTEGER; 
      4    a_file  BFILE := BFILENAME('SOME_DIR_ALIAS', 'sound_clip'); 
      5  BEGIN 
      6    UPDATE multimedia SET audio_clip = empty_clob() WHERE id = 1 
    returning 
      7  	     audio_clip into ac; 
      8   
      8  /* Open the server side file that contains the audio clip, load it  */ 
      9  	/* into the clob and then close the file.  Note, assume that the */ 
     10  	/* audio clip is only 32,000 bytes long and that it starts at */ 
     11  	/* position 1 in the file.*/ 
     12    dbms_lob.fileopen(a_file, dbms_lob.file_readonly); 
     13    amount := 26; 
     14    /* note that the destination and source offsets default to 1 */ 
     15    dbms_lob.loadfromfile(ac, a_file, amount); 
     16    dbms_lob.fileclose(a_file); 
     17    COMMIT; 
     18   
     18  /* Update all rows in the table to the audio clip we just loaded. */ 
     19    UPDATE multimedia SET audio_clip = 
     20  	     (SELECT audio_clip FROM multimedia WHERE id = 1) 
     21  	     WHERE audio_clip is null; 
     22  end; 
     23  / 
     
    PL/SQL procedure successfully completed. 
     
    SQL>  
    SQL> select id, audio_clip from multimedia; 
     
            ID 
    -----------                                                                                                                                       
    AUDIO_CLIP 
    ---------------------------------------------------------------------------                                                                    
               1                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
             2                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
             3                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
      
            ID 
    -----------                                                                                                                                       
    AUDIO_CLIP 
    ---------------------------------------------------------------------------                                                                    
             4                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
             5                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
             6                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
      
            ID 
    -----------                                                                                                                                       
    AUDIO_CLIP 
    ---------------------------------------------------------------------------                                                                    
             7                                                                    
    abcdefghijklmnopqrstuvwxyz                                                     
             8                                                                   
    abcdefghijklmnopqrstuvwxyz                                                     
             9                                                                   
    abcdefghijklmnopqrstuvwxyz                                                     
      
            ID 
    -----------                                                                                                                                       
    AUDIO_CLIP 
    ---------------------------------------------------------------------------                                                                    
            10                                                                   
    abcdefghijklmnopqrstuvwxyz                                                     
      
    10 rows selected. 
     
    SQL>  
    SQL> quit
    

LOBs in the Object Cache

When you create an object in the object cache that contains an internal LOB attribute, the LOB attribute is implicitly set to empty. You may not use this empty LOB locator to write data to the LOB. You must first flush the object, thereby inserting a row into the table and creating an empty LOB - that is, a LOB with 0 length. Once the object is refreshed in the object cache (use OCI_PIN_LATEST), the real LOB locator is read into the attribute, and you can then call the OCI LOB API to write data to the LOB.

When creating an object with a BFILE attribute, the BFILE is set to NULL. It must be updated with a valid directory alias and filename before reading from the file.

When you copy one object to another in the object cache with a LOB locator attribute, only the LOB locator is copied. This means that the LOB attribute in these two different objects contain exactly the same locator which refers to one and the same LOB value. Only when the target object is flushed is a separate, physical copy of the LOB value made, which is distinct from the source LOB value.

See Also:

"Example of a Read Consistent Locator" on page 6-26 for a description of what version of the LOB value will be seen by each object if a write is performed through one of the locators.

 

Therefore, in cases where you want to modify the LOB that was the target of the copy, you must flush the target object, refresh the target object, and then write to the LOB through the locator attribute.

LOB Buffering Subsystem

LOB Buffering

Oracle8 provides a LOB buffering subsystem (LBS) for advanced OCI based applications such as DataCartridges, Web servers, and other client-based applications that need to buffer the contents of one or more LOBs in the client's address space. The client-side memory requirement for the buffering subsystem during its maximum usage is 512K bytes. It is also the maximum amount that you can specify for a single read or write operation on a LOB that has been enabled for buffered access.

Advantages of LOB Buffering

The advantages of buffering, especially for applications that perform a series of small reads and writes (often repeatedly) to specific regions of the LOB, are two fold:

Considerations in the Use of LOB Buffering

The following caveats hold for buffered LOB operations:

LOB Buffering Operations

The Physical Structure of the LOB Buffer

For Oracle 8.0, each user session has a fixed page pool of 16 pages, which are to be shared by all LOBs accessed in buffering mode from that session. Each page has a fixed size of up to 32K bytes (not characters). A LOB's buffer consists of one or more of these pages, up to a maximum of 16 per session. The maximum amount that you ought to specify for any given buffered read or write operation is 512K bytes, remembering that under different circumstances the maximum amount you may read/write could be smaller.

Consider that a LOB is divided into fixed-size, logical regions. Each page is mapped to one of these fixed size regions, and is in essence, their in-memory copy. Depending on the input offset and amount specified for a read or write operation, Oracle8 allocates one or more of the free pages in the page pool to the LOB's buffer. A free page is one that has not been read or written by a buffered read or write operation.

Using the LOB Buffering System

For example, assuming a page size of 32K, for an input offset of 1000 and a specified read/write amount of 30000, Oracle8 reads the first 32K byte region of the LOB into a page in the LOB's buffer. For an input offset of 33000 and a read/write amount of 30000, the second 32K region of the LOB is read into a page. For an input offset of 1000, and a read/write amount of 35000, the LOB's buffer will contain two pages - the first mapped to the region 1 - 32K, and the second to the region 32K+1 - 64K of the LOB.

This mapping between a page and the LOB region is temporary until Oracle8 maps another region to the page. When you attempt to access a region of the LOB that is not already available in full in the LOB's buffer, Oracle8 allocates any available free page(s) from the page pool to the LOB's buffer. If there are no free pages available in the page pool, Oracle8 reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB's buffer and reallocates it for the current operation.

If no such page is available in the LOB's buffer, it ages out the least recently used page among the unmodified pages of other buffered LOBs in the same session. Again, if no such page is available, then it implies that all the pages in the page pool are dirty (i.e. they have been modified), and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle8 notifies this condition to the user as an error. Oracle8 never flushes and reallocates a dirty page implicitly - you can either flush them explicitly, or discard them by disabling buffering on the LOB.

To illustrate the above discussion, consider two LOBs being accessed in buffered mode - L1 and L2, each with buffers of size 8 pages. Assume that 6 of the 8 pages in L1's buffer are dirty, with the remaining 2 contain unmodified data read in from the server. Assume similar conditions in L2's buffer. Now, for the next buffered operation on L1, Oracle8 will reallocate the least recently used page from the two unmodified pages in L1's buffer. Once all the 8 pages in L1's buffer are used up for LOB writes, Oracle8 can service two more operations on L1 by allocating the two unmodified pages from L2's buffer using the least recently used policy. But for any further buffered operations on L1 or L2, Oracle8 returns an error.

If all the buffers are dirty and you attempt another read from or write to a buffered LOB, you will raise the following error:

 Error 22280: no more buffers available for operation

There are two possible causes:

  1. All buffers in the buffer pool have been used up by previous operations.

    In this case, flush the LOB(s) through the locator that is being used to update the LOB.

  2. You are trying to flush a LOB without any previous buffered update operations.

    In this case, write to the LOB through a locator enabled for buffering before attempting to flush buffers.

Flushing the LOB Buffer

The term flush refers to a set of processes. Writing data to the LOB in the buffer through the locator transforms the locator into an updated locator. Once you have updated the LOB data in the buffer through the updated locator, a flush call will

After the flush, the locator becomes a read consistent locator and can be assigned to another locator (L2 := L1).

For instance, suppose you have two locators, L1 and L2. Let us say that they are both read consistent locators and consistent with the state of the LOB data in the server. If you then update the LOB by writing to the buffer, L1 becomes an updated locator. L1 and L2 now refer to different versions of the LOB value. If you wish to update the LOB in the server, you must use L1 to retain the read consistent state captured in L2. The flush operation writes a new snapshot environment into the locator used for the flush. The important point to remember is that you must use the updated locator (L1), when you flush the LOB buffer. Trying to flush a read consistent locator will generate an error.

This raises the question: What happens to the data in the LOB buffer? There are two possibilities. In the default mode, the flush operation retains the data in the pages that were modified. In this case, when you read or write to the same range of bytes no roundtrip to the server is necessary. Note that flush in this context does not clear the data in the buffer. It also does not return the memory occupied by the flushed buffer to the client address space.


Note:

Unmodified pages may now be aged out if necessary.

 

In the second case, you set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the buffer pages, and so return the memory to the client address space. Note that flush in this context updates the LOB value on the server, returns a read consistent locator, and frees the buffer pages.

Flushing the Updated LOB

It is very important to note that you must flush a LOB that has been updated through the LBS:

Using Locators Enabled for Buffering

Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.

Saving Locator State so as to Avoid a Reselect

Suppose you want to save the current state of the LOB before further writing to the LOB buffer. In performing updates while using LOB buffering, writing to an existing buffer does not make a roundtrip to the server, and so does not refresh the snapshot environment in the locator. This would not be the case if you were updating the LOB directly without using LOB buffering. In that case, every update would involve a roundtrip to the server, and so would refresh the snapshot in the locator. In order to save the state of a LOB that has been written through the LOB buffer, you therefore need to

  1. Flush the LOB, thereby updating the LOB and the snapshot environment in the locator (L1). At this point, the state of the locator (L1) and the LOB are the same.
  2. Assign the locator (L1) used for flushing and updating to another locator (L2). At this point, the states of the two locators (L1 and L2), as well as the LOB are all identical.

L2 now becomes a read consistent locator with which you are able to access the changes made through L1 up until the time of the flush, but not after! This assignment avoids incurring a roundtrip to the server to reselect the locator into L2.

Example of LOB Buffering

The following pseudocode for an OCI program based on the lob_table schema briefly explains the concepts listed above.

OCI_BLOB_buffering_program()
{
   int            amount;
   int            offset;
   OCILobLocator  lbs_loc1, lbs_loc2, lbs_loc3;
   void          *buffer;
   int            bufl;

   -- Standard OCI initialization operations - logging on to
   -- server, creating and initializing bind variables etc.
  
   init_OCI();

   -- Establish a savepoint before start of LBS operations 
   exec_statement("savepoint lbs_savepoint");
  
   -- Initialize bind variable to BLOB columns from buffered 
   -- access: 
   exec_statement("select b_lob into lbs_loc1 from lob_table
       where key_value = 12");
   exec_statement("select b_lob into lbs_loc2 from lob_table
       where key_value = 12 for update");
   exec_statement("select b_lob into lbs_loc2 from lob_table
       where key_value = 12 for update");
      
   -- Enable locators for buffered mode access to LOB:
   OCILobEnableBuffering(lbs_loc1);
   OCILobEnableBuffering(lbs_loc2);
   OCILobEnableBuffering(lbs_loc3);
  
   -- Read 4K bytes through lbs_loc1 starting from offset 1:
   amount = 4096; offset = 1; bufl = 4096;
   OCILobFileRead(.., lbs_loc1, offset, &amount, buffer, bufl,   
      ..);
      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from 
          -- the server into a page (call it page_A) in the LOB's
          -- client-side buffer.
          -- lbs_loc1 is a read consistent locator.
         
          -- Write 4K of the LOB throgh lbs_loc2 starting from 
          -- offset 1:      
          amount = 4096; offset = 1; bufl = 4096;
          buffer = populate_buffer(4096);
          OCILobFileWrite(.., lbs_loc2, offset, amount, buffer, 
              bufl, ..);
      
      if (exception)
          goto exception_handler;
          -- This will read the first 32K bytes of the LOB from
          -- the server into a new page (call it page_B) in the
          -- LOB's buffer, and modify the contents of this page 
          -- with input buffer contents.
          -- lbs_loc2 is an updated locator.
      
          -- Read 20K bytes through lbs_loc1 starting from  
          -- offset 10K      
          amount = 20480; offset = 10240;
          OCILobFileRead(.., lbs_loc1, offset, &amount, buffer, 
              bufl, ..);
 
      if (exception)
        goto exception_handler;
          -- Read directly from page_A into the user buffer. 
          -- There is no round-trip to the server because the
          -- data is already in the client-side buffer.

          -- Write 20K bytes through lbs_loc2 starting from offset 
          -- 10K
          amount = 20480; offset = 10240; bufl = 20480;
          buffer = populate_buffer(20480);
          OCILobFileWrite(.., lbs_loc2, offset, amount, buffer, 
               bufl, ..);
      
      if (exception)
          goto exception_handler;
          -- The contents of the user buffer will now be written
          -- into page_B without involving a round-trip to the 
          -- server.  This avoids making a new LOB version on the
          -- server and writing redo to the log.  
                    
          -- The following write through lbs_loc3 will also  
          -- result in an error: 
          amount = 20000; offset = 1000; bufl = 20000;
          buffer = populate_buffer(20000);
          OCILobFileWrite(.., lbs_loc3, offset, amount, buffer, 
               bufl, ..);

      if (exception)
          goto exception_handler;
          -- No two locators can be used to update a buffered LOB 
          -- through the buffering subsystem
     
      -- The following update through lbs_loc3 will also           
      -- result in an error
      OCILobFileCopy(.., lbs_loc3, lbs_loc2, ..);

      if (exception)
          goto exception_handler;
          -- Locators enabled for buffering cannot be used with 
          -- operations like Append, Copy, Trim etc.
     
      -- When done, flush LOB's buffer to the server: 
      OCILobFlushBuffer(.., lbs_loc2, OCI_LOB_BUFFER_NOFREE);
  
      if (exception)
         goto exception_handler;
         -- This flushes all the modified pages in the LOB's buffer, 
         -- and resets lbs_loc2 from updated to read consistent 
         -- locator. The modified pages remain in the buffer 
         -- without freeing memory.  These pages can be aged 
         -- out if necessary.
      
      -- Disable locators for buffered mode access to LOB */
      OCILobDisableBuffering(lbs_loc1);
      OCILobDisableBuffering(lbs_loc2);
      OCILobDisableBuffering(lbs_loc3);

      if (exception)
         goto exception_handler;
         -- This disables the three locators for buffered access, 
         -- and frees up the LOB's buffer resources.
  
      exception_handler:
      handle_exception_reporting();
      exec_statement("rollback to savepoint lbs_savepoint");
} 

User Guidelines for Best Performance Practices

Working with Varying-Width Character Data

Varying width character data is not supported for BLOBs, CLOBs and NCLOBs. However, BLOBs can contain any data. Since CLOBs/NCLOBs cannot store varying width character sets, you may be tempted to store varying width characters in a BLOB and do the character set conversion yourself. The drawback is that you need to do these conversions, and also that the offset and amount parameters are in terms of bytes instead of characters. So, the danger is that you could retrieve text information from the BLOB but cut a varying width character in half because the byte amount you specified was not correct. Consequently, we caution against taking this course of action.

BFILEs likewise can contain any data including text. But, once again, in storing the text, you will need to do your own character set conversions and offset and amount parameters will be in bytes.

As stated above, CLOBs store fixed width single byte data, and NCLOBs store fixed width multi byte data. Neither supports varying width data.

You might expect from this that if the database character set is varying width, and a user tries to create a table with a CLOB column, the create will fail. This is almost the case, but the reality is a little different.

The same holds true for NCLOBs and the database national character set.

The reason for allowing the SQL DDL to pass while making sure that the SQL DML fails if the user tries to insert a non-null value into the LOB that has a varying width character set is so that the same table can be created and exist in several different databases regardless of the underlying CHAR (NCHAR) character set. The user can write one application and modify it slightly for databases where the CHAR (NCHAR) character set is varying width such that the insert sets the varying width LOB to NULL.

LOB Reference

Reference Overview

Although not explicitly marked, this section is organized on the following basis.

EMPTY_BLOB() and EMPTY_CLOB() Functions

You can use the special functions EMPTY_BLOB () and EMPTY_CLOB () in INSERT or UPDATE statements of SQL DML to initialize a NULL or non-NULL internal LOB to empty. These are available as special functions in Oracle8 SQL DML, and are not part of the DBMS_LOB package.

Before you can start writing data to an internal LOB using OCI or the DBMS_LOB package, the LOB column must be made non-null, that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value to empty by using the function EMPTY_BLOB() in the VALUES clause of an INSERT statement. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB().

Syntax

FUNCTION EMPTY_BLOB() RETURN BLOB;
FUNCTION EMPTY_CLOB() RETURN CLOB;

Parameters

None.

Return Values

EMPTY_BLOB() returns an empty locator of type BLOB and EMPTY_CLOB() returns an empty locator of type CLOB, which can also be used for NCLOBs.

Pragmas

None.

Exceptions

An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.

Examples

The following example shows EMPTY_BLOB() usage with SQL DML:

 
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), 'abcde', NULL, NULL);
UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001; 
INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL, NULL); 

The following example shows the correct and erroneous usage of EMPTY_BLOB() and EMPTY_CLOB () in PL/SQL programs:

DECLARE 
  loba         BLOB; 
  lobb         CLOB; 
  read_offset  INTEGER; 
  read_amount  INTEGER; 
  rawbuf       RAW(20); 
  charbuf      VARCHAR2(20);
BEGIN
  loba := EMPTY_BLOB(); 
  read_amount := 10; read_offset := 1;
  -- the following read will fail 
  dbms_lob.read(loba, read_amount, read_offset, rawbuf); 
   
  -- the following read will succeed;
  UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 
         1002 RETURNING c_lob INTO lobb;
dbms_lob.read(lobb, read_amount, read_offset, charbuf); 
  dbms_output.put_line('lobb value: ' || charbuf);

BFILENAME() Function

The BFILENAME() function should be called as part of SQL INSERT to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.

The DIRECTORY object represented by the directory_alias parameter to this function must already be defined using SQL DDL before this function is called in SQL DML or a PL/SQL program. You can call the CREATE DIRECTORY() command after BFILENAME(). However, the target object must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen()or DBMS_LOB.FILEOPEN()).

Note that BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that was initialized by the BFILENAME() function.

You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.

The 'directory_alias' parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration. This is described in the examples.

See Also:

"DIRECTORY Name Specification" on page 6-17.

 

Syntax

FUNCTION BFILENAME(directory_alias IN VARCHAR2,
                  filename IN VARCHAR2)
RETURN BFILE;

See Also:

"DIRECTORY Name Specification" on page 6-17 for information about the use of uppercase letters in the directory name, and OCILobFileSetName() in Oracle Call Interface Programmer's Guide for an equivalent OCI based routine.

 

Parameters

Table 6-1 FILENAME Parameters
Parameter Name   Meaning  

directory_alias  

The name of the DIRECTORY object that was created using the CREATE DIRECTORY command.  

filename  

The name of the operating system file on the server.  

Return Values

BFILE locator upon success.

NULL if directory_alias has not been defined previously.

Pragmas

None.

Exceptions

None.

Example

To access a file 'scott.dat' located in SCOTT_DIR, and file 'mary.dat' located in Mary_Dir, the BFILE locators must be initialized as shown below.

DECLARE
    fil_1, fil_2 BFILE;
  result INTEGER;
BEGIN
    fil_1 := BFILENAME(`SCOTT_DIR', `scott.dat');
    fil_2 := BFILENAME(`Mary_Dir', `mary.dat');
    DBMS_LOB.FILEOPEN(fil_1);
    DBMS_LOB.FILEOPEN(fil_2);
    result := DBMS_LOB.COMPARE(fil_1, fil_2);
    IF (result != 0)
    THEN
      DBMS_OUTPUT.PUT_LINE(`The two files are different');
    END IF;
    DBMS_LOB.FILECLOSE(fil_1);
    DBMS_LOB.FILECLOSE(fil_2);
    -- FILEOPEN will fail with the following initialization (in
       lowercase)
    fil_1 := BFILENAME(`scott_dir', `scott.dat');
    DBMS_LOB.FILEOPEN(fil_1);

-- this is an error
END;

INSERT INTO lob_table VALUES (21, NULL, NULL, NULL, 
    BFILENAME(`SCOTT_DIR',`scott.dat'));
INSERT INTO lob_table VALUES (12, NULL, NULL, NULL, 
    BFILENAME(`Mary_Dir',`mary.dat'));

DECLARE
    fil_1, fil_2 BFILE;
    result       INTEGER;

BEGIN
    SELECT f_lob INTO fil_1 FROM lob_table WHERE key_value = 21;
    SELECT f_lob INTO fil_2 FROM lob_table WHERE key_value = 12;
    DBMS_LOB.FILEOPEN(fil_1);
    DBMS_LOB.FILEOPEN(fil_2);
    result := DBMS_LOB.COMPARE(fil_1, fil_2);
    IF (result != 0)
    THEN
      DBMS_OUTPUT.PUT_LINE(`The two files are different');
    END IF;
    DBMS_LOB.FILECLOSE(fil_1);
    DBMS_LOB.FILECLOSE(fil_2);
END;

See Also:

DMBS_LOB.FILEGETNAME().

 

Using the OCI to Manipulate LOBs

The OCI includes functions that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are mentioned briefly below.

See Also:

Oracle Call Interface Programmer's Guide for detailed documentation, including parameters, parameter types, return values, and example code.

 

Table 6-2 OCI Functions for LOB Operations
OCI Function   Description  

OCILobAppend()  

Appends LOB value to another LOB.  

OCILobAssign()  

Assigns one LOB locator to another.  

OCILobCharSetForm()  

Returns the character set form of a LOB.  

OCILobCharSetId()  

Returns the character set ID of a LOB.  

OCILobCopy()  

Copies a portion of a LOB into another LOB.  

OCILobDisableBuffering()  

Disable the buffering subsystem use.  

OCILobEnableBuffering()  

Use the LOB buffering subsystem for subsequent reads and writes of LOB data.  

OCILobErase()  

Erases part of a LOB, starting at a specified offset.  

OCILobFileClose()  

Closes an open BFILE.  

OCILobFileCloseAll()  

Closes all open BFILEs.  

OCILobFileExists()  

Checks whether a BFILE exists.  

OCILobFileGetName()  

Returns the name of a BFILE.  

OCILobFileIsOpen()  

Checks whether a BFILE is open.  

OCILobFileOpen()  

Opens a BFILE.  

OCILobFileSetName()  

Sets the name of a BFILE in a locator.  

OCILobFlushBuffer()  

Flush changes made to the LOB buffering subsystem to the database (sever)  

OCILobGetLength()  

Returns the length of a LOB or a BFILE.  

OCILobIsEqual()  

Checks whether two LOB locators refer to the same LOB.  

OCILobLoadFromFile()  

Loads BFILE data into an internal LOB.  

OCILobLocatorIsInit()  

Checks whether a LOB locator is initialized.  

OCILobLocatorSize()  

Returns the size of a LOB locator.  

OCILobRead()  

Reads a specified portion of a non-null LOB or a BFILE into a buffer.  

OCILobTrim()  

Truncates a LOB.  

OCILobWrite()  

Writes data from a buffer into a LOB, overwriting existing data.  

The following chart compares the two interfaces in terms of LOB access:

Table 6-3 Comparison of DBMS_LOB and OCI Interfaces regarding LOB access
OCI (ociap.h)   DBMS_LOB (dbmslob.sql)  

N/A  

DBMS_LOB.COMPARE()  

N/A  

DBMS_LOB.INSTR()  

N/A  

DBMS_LOB.SUBSTR()  

OCILobAppend  

DBMS_LOB.APPEND()  

OCILobAssign  

N/A [use Pl/SQL assign operator]  

OCILobCharSetForm  

N/A  

OCILobCharSetId  

N/A  

OCILobCopy  

DBMS_LOB.COPY()  

OCILobDisableBuffering  

N/A  

OCILobEnableBuffering  

N/A  

OCILobErase  

DBMS_LOB.ERASE()  

OCILobFileClose  

DBMS_LOB.FILECLOSE()  

OCILobFileCloseAll  

DBMS_LOB.FILECLOSEALL()  

OCILobFileExists  

DBMS_LOB.FILEEXISTS()  

OCILobFileGetName  

DBMS_LOB.FILEGETNAME()  

OCILobFileIsOpen  

DBMS_LOB.FILEISOPEN()  

OCILobFileOpen  

DBMS_LOB.FILEOPEN()  

OCILobFileSetName  

N/A (use BFILENAME operator)  

OCILobFlushBuffer  

N/A  

OCILobGetLength  

DBMS_LOB.GETLENGTH()  

OCILobIsEqual  

N/A [use Pl/SQL equal operator]  

OCILobLoadFromFile  

DBMS_LOB.LOADFROMFILE()  

OCILobLocatorIsInit  

N/A [always initialize]  

OCILobRead  

DBMS_LOB.READ()  

OCILobTrim  

DBMS_LOB.TRIM()  

OCILobWrite  

DBMS_LOB.WRITE()  

DBMS_LOB Package

The DBMS_LOB package provides routines to access BLOBs, CLOBs, NCLOBs, and BFILEs. You can use DBMS_LOB for access and manipulation of specific parts of a LOB, as well as complete LOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations on BFILEs.

All DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external filesystem.

For internal LOBs, you must first use SQL DDL to define tables that contain LOB columns, and subsequently SQL DML to initialize or populate the locators in these LOB columns.

See Also:

"LOB Locator Operations" on page 6-23

 

For external LOBs, you must ensure that a DIRECTORY object that represents a valid, existing physical directory has been defined, and physical files exist with read permission for Oracle. If your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format.

See Also:

"BFILE Security" on page 6-17

 

.

Once the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value. Examples provided with each DBMS_LOB routine will illustrate this in the following sections.

Package Routines

The routines that can modify BLOB, CLOB, and NCLOB values are:

The routines that read or examine LOB values are:

The read-only routines specific to BFILEs are:

Datatypes

Parameters for the DBMS_LOB routines use the datatypes:

Type Definitions

The DBMS_LOB package defines no special types. NCLOB is a special case of CLOBs for fixed-width, multi-byte national character sets. The clause 'ANY_CS' in the specification of DBMS_LOB routines for CLOBs allows them to accept a CLOB or NCLOB locator variable as input.

See Also:

"LOB Datatypes" in the Oracle8 SQL Reference

 

Constants

The DBMS_LOB package defines the following constants.

LOBMAXSIZE                4294967295
FILE_READONLY              0

The maximum LOB size supported in Oracle 8.0 is 4 Gigabytes (232). However, the amount and offset parameters of the package can have values in the range 1 through 4294967295 (232-1).

The PL/SQL 3.0 language specifies the maximum size of a RAW or VARCHAR2 variable to be 32767 bytes.


Note:

The value 32767 bytes is represented by MAXBUFSIZE in the following sections.

 

DBMS_LOB Exceptions

A DBMS_LOB function or procedure can raise any of the named exceptions shown in Table 6-4 .

Table 6-4 DBMS_LOB Exceptions
Exception   Code in error.msg   Meaning  

INVALID_ARGVAL  

21560  

"argument %s is null, invalid, or out of range"  

ACCESS_ERROR  

22925  

Attempt to read/write beyond maximum LOB size on <n>.  

NO_DATA_FOUND  

1403  

EndofLOB indicator for looping read operations  

VALUE_ERROR  

6502  

Invalid value in parameter.  

access_error 22925 "operation would exceed maximum size allowed for a LOB"

noexist_directory 22285 "%s failed - directory does not exist"

nopriv_directory 22286 "%s failed - insufficient privileges on directory"

invalid_directory 22287 "%s failed - invalid or modified directory"

invalid_operation 22288 "%s operation failed"

unopened_file 22289 "cannot perform %s operation on an unopened file"

open_toomany 22290 "%s failed - max limit reached on number of open files"

DBMS_LOB functions return a NULL value if any of the input parameters to these routines are NULL or invalid, whereas DBMS_LOB procedures will raise exceptions. This behavior is consistent with Oracle8 SQL functions, and procedures in other built-in PL/SQL packages in Oracle8.


DBMS_LOB Security

This section describes the security domain for DBMS_LOB routines operating on internal LOBs (i.e. BLOB, CLOB and NCLOB) when you are using the Oracle server.


Note:

Any DBMS_LOB routine called from an anonymous PL/SQL block is executed using the privileges of the current user. Any DBMS_LOB routine called from a stored procedure is executed using the privileges of the owner of the stored procedure.

 

You can provide secure access to BFILEs using the DIRECTORY feature discussed in "BFILENAME() Function" on page 6-62.

DBMS_LOB General Usage Notes

  1. Length, amount and offset parameters are specified in terms of bytes for BLOBs and BFILES, and characters for CLOBs and NCLOBs.
  2. Note that PL/SQL 3.0 language specifies that constraints for both RAW and VARCHAR2 buffers are specified in terms of bytes. For example, if you declare a variable to be
            charbuf VARCHAR2(3000)

charbuf can hold 3000 single byte characters or a 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB routines for CLOBs and NCLOBs.

  • You must ensure that the character set of the VARCHAR2 buffer in a DBMS_LOB routine for CLOBs exactly matches that of the CLOB. The package specification partially ensures this with the %CHARSET clause, but in certain cases where the fixed-width character set is actually a subset of a varying width character set, it may not be possible to enforce this.

    Hence, it is your responsibility to provide a buffer with the correct character set and enough buffer size for holding all the characters. No translation on the basis of session initialization parameters is performed.

  • Only positive, non-zero values (i.e. a value greater than or equal to 1) are allowed for the AMOUNT and OFFSET parameters. This implies that: negative offsets and ranges observed in Oracle SQL string functions and operators are not allowed.
  • Unless otherwise stated, the default value for an offset parameter is 1, which indicates the first byte in the BLOB or BFILE data, and the first character in the CLOB or NCLOB value. No default values are specified for the AMOUNT parameter - you have to input the values explicitly.
  • You are responsible for locking the row containing the destination internal LOB before calling any routines that modify the LOB such as APPEND, COPY, ERASE, TRIM, or WRITE. These routines do not implicitly lock the row containing the LOB.
  • BFILE-Specific Usage Notes

    1. Recalling that COMPARE(), INSTR() and SUBSTR() are DBMS_LOB specific, the operations COMPARE(), INSTR(), READ(), SUBSTR(), FILECLOSE(), FILECLOSEALL() and LOADFROMFILE() operate only on an opened BFILE locator, that is, a successful FILEOPEN() call must precede a call to any of these routines.
    2. For the functions FILEEXISTS(), FILEGETNAME() and GETLENGTH(), a file's open/close status is unimportant, however the file must exist physically and you must have adequate privileges on the DIRECTORY object and the file.
    3. The DBMS_LOB package does not support any concurrency control mechanism for BFILE operations.
    4. In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL() routine to close all files opened in the session, and resume file operations from the beginning.
    5. If you are the creator of a DIRECTORY or have system privileges, use the CREATE OR REPLACE, DROP and REVOKE statements in SQL with extreme caution.

      See Also:

      "Guidelines for DIRECTORY Usage" on page 6-19.

       

      If you or other grantees of a particular directory object have several open files in a session, any of the above commands can adversely affect file operations. In the event of such abnormal termination, your only choice is to invoke a program or anonymous block that calls FILECLOSEALL(), reopen your files, and restart your file operations.

    6. All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE.

      See Also:

      "Maximum Number of Open BFILEs" on page 6-20.

       

      In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES.

      In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because, once an exception occurs, only the exception handler will have access to the BFILE variable in its most current state.

      See Also:

      "Closing BFILEs after Program Termination" on page 6-21.

       

      Once the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.

      For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception.

      DECLARE 
             fil bfile; 
             pos INTEGER; 
             amt binary_INTEGER; 
             buf RAW(40); 
      BEGIN 
             SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21; 
             dbms_lob.FILEOPEN(fil, dbms_lob.file_readonly);   
             amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; 
             dbms_lob.read(fil, amt, pos, buf); 
             dbms_output.put_line('Read F1 past EOF: '|| 
                 utl_raw.cast_to_varchar2(buf));
             dbms_lob.fileclose(fil); 
      END;
            
      ORA-01403: no data found 
      ORA-06512: at "SYS.DBMS_LOB", line 373 
      ORA-06512: at line 10 
      

    Once the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. So the solution is to use an exception handler as shown below:

    DECLARE
         fil bfile; 
         pos INTEGER;
         amt binary_INTEGER; 
         buf RAW(40); 
    BEGIN 
         SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
         dbms_lob.FILEOPEN(fil, dbms_lob.file_readonly);   
         amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := ''; 
         dbms_lob.read(fil, amt, pos, buf); 
         dbms_output.put_line('Read F1 past EOF: '|| 
              utl_raw.cast_to_varchar2(buf)); 
         dbms_lob.fileclose(fil); 
         exception 
         WHEN no_data_found 
         then 
           BEGIN 
             dbms_output.put_line('End of File reached. Closing file'); 
             dbms_lob.fileclose(fil); 
             -- or dbms_lob.filecloseall if appropriate 
           END; 
    END; 
         / 
    
    Statement processed. 
    End of File reached. Closing file
    

    In general, it is good coding practice to ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal/abnormal termination of the block.

    DBMS_LOB.APPEND() Procedure

    You can call the internal APPEND() procedure to append the contents of a source internal LOB to a destination LOB. The procedure appends the complete source LOB. There are two overloaded APPEND() procedures, as shown in the syntax section below.

    Syntax

    PROCEDURE APPEND (dest_lob IN OUT  BLOB, 
                      src_lob  IN      BLOB); 
    PROCEDURE APPEND (dest_lob IN OUT  CLOB  CHARACTER SET ANY_CS, 
                      src_lob  IN      CLOB  CHARACTER SET dest_lob%CHARSET);
    

    Parameters

    Table 6-5 APPEND Parameters
    Parameter Name   Meaning  

    dest_lob  

    The locator for the internal LOB to which the data is to be appended.  

    src_lob  

    The locator for the internal LOB from which the data is to be read.  

    Exceptions

    VALUE_ERROR, if either the source or the destination LOB is null.

    Example

    PROCEDURE Example_1a IS
        dest_lob, src_lob  BLOB;
    BEGIN
        -- get the LOB locators
        -- note that the FOR UPDATE clause locks the row
        SELECT b_lob INTO dest_lob
            FROM lob_table
            WHERE key_value = 12 FOR UPDATE;
        SELECT b_lob INTO src_lob
            FROM lob_table
            WHERE key_value = 21;
        DBMS_LOB.APPEND(dest_lob, src_lob);
        COMMIT;
    EXCEPTION
        WHEN some_exception
        THEN handle_exception;
    END;
    
    PROCEDURE Example_1b IS
        dest_lob, src_lob  BLOB;
    BEGIN
        -- get the LOB locators
        -- note that the FOR UPDATE clause locks the row
        SELECT b_lob INTO dest_lob
            FROM lob_table
            WHERE key_value = 12 FOR UPDATE;
        SELECT b_lob INTO src_lob
            FROM lob_table
            WHERE key_value = 12;
        DBMS_LOB.APPEND(dest_lob, src_lob);
        COMMIT;
    EXCEPTION
        WHEN some_exception
        THEN handle_exception;
    END;
    

    DBMS_LOB.COMPARE() Function

    You can call the COMPARE() function to compare two entire LOBs, or parts of two LOBs. You can only compare LOBs of the same datatype. That is, you compare LOBs of BLOB type with other BLOBs, and CLOBs with CLOBs, and BFILEs with BFILEs. For BFILEs, the file has to be already opened using a successful FILEOPEN() operation for this operation to succeed.

    COMPARE() returns zero if the data exactly matches over the range specified by the offset and amount parameters. Otherwise, a non-zero INTEGER is returned.

    For fixed-width n-byte CLOBs, if the input amount for COMPARE is specified to be greater than (4294967295/n), then COMPARE matches characters in a range of size (4294967295/n), or Max(length(clob1), length(clob2)), whichever is lesser.

    Syntax

    FUNCTION COMPARE (
        lob_1            IN BLOB,
        lob_2            IN BLOB,
        amount           IN INTEGER := 4294967295,
        offset_1         IN INTEGER := 1,
        offset_2         IN INTEGER := 1)
    RETURN INTEGER;
    
    FUNCTION COMPARE (
        lob_1            IN CLOB  CHARACTER SET ANY_CS,
        lob_2            IN CLOB  CHARACTER SET lob_1%CHARSET,
        amount           IN INTEGER := 4294967295,
        offset_1         IN INTEGER := 1,
        offset_2         IN INTEGER := 1)
    RETURN INTEGER; 
    FUNCTION COMPARE (
        lob_1            IN BFILE,
        lob_2            IN BFILE,
        amount           IN INTEGER,
        offset_1         IN INTEGER := 1,
        offset_2         IN INTEGER := 1)
    RETURN INTEGER;
    

    Parameters

    Table 6-6 COMPARE Parameters
    Parameter Name   Meaning  

    lob_1  

    LOB locator of first target for comparison.  

    lob_2  

    LOB locator of second target for comparison  

    amount  

    Number of bytes or characters to compare over.  

    offset_1  

    Offset in bytes or characters on the first LOB (origin: 1) for the comparison.  

    offset_2  

    Offset in bytes or characters on the first LOB

    (origin: 1) for the comparison.  

    Return Values

      Pragmas

           PRAGMA RESTRICT_REFERENCES(compare, WNDS, WNPS, RNDS, RNPS);
      

      Exceptions

      For BFILE operations, UNOPENED_FILE if the file was not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file.

      Examples

      PROCEDURE Example2a IS
          lob_1, lob_2      BLOB;
          retval            INTEGER;
      BEGIN
          SELECT b_col INTO lob_1 FROM lob_table
              WHERE key_value = 45;
          SELECT b_col INTO lob_2 FROM lob_table
              WHERE key_value = 54;
          retval := DBMS_LOB.COMPARE(lob_1, lob_2, 5600, 33482,
               128);
          IF retval = 0 THEN
            ;    /* process compared code */
          ELSE
            ;    /* process not compared code */
          END IF;
      END;
      
      PROCEDURE Example_2b IS
          fil_1, fil_2       BFILE;    
          retval             INTEGER;
      BEGIN
          SELECT f_lob INTO fil_1 FROM lob_table WHERE key_value = 45;
          SELECT f_lob INTO fil_2 FROM lob_table WHERE key_value = 54;
          DBMS_LOB.FILEOPEN(fil_1, DBMS_LOB.FILE_READONLY);
          DBMS_LOB.FILEOPEN(fil_2, DBMS_LOB.FILE_READONLY);
          retval := DBMS_LOB.COMPARE(fil_1, fil_2, 5600,
                                        3348276, 2765612);
          IF (retval = 0) 
          THEN
              ; /* process compared code */
          ELSE
              ; /* process not compared code */
          END IF;
          DBMS_LOB.FILECLOSE(fil_1);
          DBMS_LOB.FILECLOSE(fil_2);
      END;
      

      DBMS_LOB.COPY() Procedure

      You can call the COPY() procedure to copy all, or a part of, a source internal LOB to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.

      If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, existing data is overwritten.

      It is not an error to specify an amount that exceeds the length of the data in the source LOB. Thus, you can specify a large amount to copy from the source LOB which will copy data from the src_offset to the end of the source LOB.

      Syntax

      PROCEDURE COPY (
        dest_lob    IN OUT BLOB,
        src_lob     IN     BLOB,
        amount      IN     INTEGER,
        dest_offset IN     INTEGER := 1,
        src_offset  IN     INTEGER := 1);
      
      PROCEDURE COPY ( 
        dest_lob    IN OUT CLOB  CHARACTER SET ANY_CS,
        src_lob     IN     CLOB  CHARACTER SET dest_lob%CHARSET,
        amount      IN     INTEGER,
        dest_offset IN     INTEGER := 1,
        src_offset  IN     INTEGER := 1);
      

      Parameters

      Table 6-7 COPY Parameters
      Parameter Name   Meaning  

      dest_lob  

      LOB locator of the copy target.  

      src_lob  

      LOB locator of source for the copy.  

      amount  

      Number of bytes or characters to copy.  

      dest_offset  

      Offset in bytes or characters in the destination LOB (origin: 1) for the start of the copy.  

      src_offset  

      Offset in bytes or characters in the source LOB

      (origin: 1) for the start of the copy.  

      Return Value

      None.

      Pragmas

      None.

      Exceptions

      VALUE_ERROR, if any of the input parameters are NULL or invalid. INVALID_ARGVAL, if

      • src_offset or dest_offset < 1
      • src_offset or dest_offset > LOBMAXSIZE
      • amount < 1
      • amount > LOBMAXSIZE

    Example

    PROCEDURE Example_3a IS
        lobd, lobs     BLOB;
        amt            INTEGER := 3000;
    BEGIN
        SELECT b_col INTO lobd
            FROM lob_table
            WHERE key_value = 12 FOR UPDATE;
        SELECT b_col INTO lobs
            FROM lob_table
            WHERE key_value = 21;
        DBMS_LOB.COPY(lobd, lobs, amt);
        COMMIT;
       EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    
    PROCEDURE Example_3b IS
        lobd, lobs     BLOB;
        amt            INTEGER := 3000;
    BEGIN
        SELECT b_col INTO lobd
            FROM lob_table
            WHERE key_value = 12 FOR UPDATE;
        SELECT b_col INTO lobs
            FROM lob_table
            WHERE key_value = 12;
        DBMS_LOB.COPY(lobd, lobs, amt);
        COMMIT;
       EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    

    DBMS_LOB.ERASE() Procedure

    You can call the ERASE() procedure to erase an entire internal LOB, or part of an internal LOB. The offset parameter specifies the starting offset for the erasure, and the amount parameter specifies the number of bytes or characters to erase.

    When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.

    The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.

    Syntax

    PROCEDURE ERASE (
        lob_loc           IN OUT        BLOB,
        amount            IN OUT        INTEGER,
        offset            IN            INTEGER := 1);
    
    PROCEDURE ERASE (
        lob_loc           IN OUT        CLOB,
        amount            IN OUT        INTEGER,
        offset            IN            INTEGER := 1);
    

    Parameters

    Table 6-8 ERASE Parameters
    Parameter Name   Meaning  

    lob_loc  

    Locator for the LOB to be erased.  

    amount  

    Number of bytes (for BLOBs) or characters (for CLOBs) to be erased.  

    offset  

    Absolute offset from the beginning of the LOB in bytes (for BLOBs) or characters (CLOBs).  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    VALUE_ERROR, if any input parameter is NULL.

    INVALID_ARGVAL, if

    Example

    PROCEDURE Example_4 IS
        lobd       BLOB;
        amt        INTEGER := 3000;
    BEGIN
        SELECT b_col INTO lobd
            FROM lob_table
            WHERE key_value = 12 FOR UPDATE;
        DBMS_LOB.ERASE(dest_lob, amt, 2000);
        COMMIT;
    END;
    

    See Also:

    DBMS_LOB.TRIM()

     

    DBMS_LOB.FILECLOSE() Procedure

    You can call the FILECLOSE() procedure to close a BFILE that has already been opened via the input locator. Note that Oracle has only read-only access to BFILEs. This means that BFILEs cannot be written through Oracle.

    Syntax

    PROCEDURE FILECLOSE (
        file_loc IN OUT BFILE); 
    

    Parameter

    Table 6-9 FILECLOSE Parameter
    Parameter Name   Meaning  

    file_loc  

    Locator for the BFILE to be closed.  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    VALUE_ERROR, if NULL input value for file_loc. UNOPENED_FILE if the file was not opened with the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.

    Example

    PROCEDURE Example_5 IS
        fil BFILE;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
        DBMS_LOB.FILEOPEN(fil);
        -- file operations
        DBMS_LOB.FILECLOSE(fil);
        EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    

    See Also:

    DBMS_LOB.FILEOPEN(), DBMS_LOB.FILECLOSEALL()

     

    DBMS_LOB.FILECLOSEALL() Procedure

    You can call the FILECLOSEALL() procedure to close all BFILEs opened in the session.

    Syntax

    PROCEDURE FILECLOSEALL; 
    

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    UNOPENED_FILE, if no file has been opened in the session.

    Example

    PROCEDURE Example_6 IS
        fil BFILE;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
        DBMS_LOB.FILEOPEN(fil);
        -- file operations
        DBMS_LOB.FILECLOSEALL;
        EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    

    See Also:

    DBMS_LOB.FILEOPEN(), DBMS_LOB.FILECLOSE()

     

    DBMS_LOB.FILEEXISTS() Function

    You can call the FILEEXISTS() function to find out if a given BFILE locator points to a file that actually exists on the server's filesystem.

    Syntax

    FUNCTION FILEEXISTS (
          file_loc     IN    BFILE)
    RETURN INTEGER; 
    

    Parameter

    Table 6-10 FILEEXISTS Parameter
    Parameter Name   Meaning  

    file_loc  

    Locator for the BFILE.  

    Return Values

    INTEGER: 1 if the physical file exists, 0 if it does not exist.

    NULL, if:

    • file_loc is NULL
    • file_loc does not have the necessary directory and OS privileges
    • file_loc cannot be read because of an OS error.

    Pragmas

    PRAGMA RESTRICT_REFERENCES(fileexists, WNDS, RNDS, WNPS, RNPS);
    

    Exceptions

    NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened.

    Example

    PROCEDURE Exsmple_7 IS
        fil BFILE;
    BEGIN    
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
        IF (DBMS_LOB.FILEEXISTS(fil)) 
        THEN
            ; -- file exists code
        ELSE
            ; -- file does not exist code
        END IF;
        EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    

    See Also:

    DBMS_LOB.FILEISOPEN

     

    DBMS_LOB.FILEGETNAME() Procedure

    You can call the FILEGETNAME() procedure to determine the dir_alias and filename, given a BFILE locator. This function only indicates the directory alias name and filename assigned to the locator, not if the physical file or directory actually exists. Maximum constraint values for the dir_alias buffer is 30, and for the entire pathname is 2000.

    Syntax

    PROCEDURE FILEGETNAME (
          file_loc   IN    BFILE, 
          dir_alias  OUT   VARCHAR2
          filename   OUT   VARCHAR2); 
    

    Parameters

    Table 6-11 FILEGETNAME Parameters
    Parameter Name   Meaning  

    file_loc  

    Locator for the BFILE.  

    dir_alias  

    Directory alias  

    filename  

    Name of the BFILE  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    VALUE_ERROR, if any of the input parameters are NULL or invalid. INVALID_ARGVAL, if dir_alias or filename are NULL.

    Example

    PROCEDURE Example_8 IS
        fil BFILE;
        dir_alias VARCHAR2(30);
        name VARCHAR2(2000);
    BEGIN
        IF (DBMS_LOB.FILEEXISTS(fil))
        THEN
            DBMS_LOB.FILEGETNAME(fil, dir_alias, name);
            DBMS_OUTPUT.PUT_LINE ("Opening " || dir_alias || name);
            DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
            -- file operations
            DBMS_OUTPUT.FILECLOSE(fil); 
        END IF;
    END;
    

    See Also:

    BFILENAME() function

     

    DBMS_LOB.FILEISOPEN() Function

    You can call the FILEISOPEN() function to find out whether a BFILE was opened with the give FILE locator. If the input FILE locator was never passed to the DBMS_LOB.FILEOPEN procedure, the file is considered not to be opened by this locator. However, a different locator may have this file open. In other words, openness is associated with a specific locator.

    Syntax

    FUNCTION FILEISOPEN (
          file_loc   IN    BFILE)
    RETURN INTEGER; 
    

    Parameter

    Table 6-12 FILEISOPEN Parameter
    Parameter Name   Meaning  

    file_loc  

    Locator for the BFILE.  

    Return Values

    Integer.

    Pragmas

    PRAGMA RESTRICT_REFERENCES(fileisopen, WNDS, RNDS, WNPS, RNPS);
    

    Exceptions

    NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened. INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.

    Example

    PROCEDURE Example_9 IS
    DECLARE
        fil      BFILE;
        pos      INTEGER;
        pattern  VARCHAR2(20);
    BEGIN
        SELECT f_lob INTO fil FROM lob_table 
            WHERE key_value = 12;
        -- open the file
        IF (FILEISOPEN(fil))
        THEN
            pos := DBMS_LOB.INSTR(fil, pattern, 1025, 6);
             -- more file operations
             DBMS_LOB.FILECLOSE(fil);
       ELSE
            ; -- return error
        END IF;
    END;
    

    See Also:

    DBMS_LOB.FILEEXISTS.

     

    DBMS_LOB.FILEOPEN() Procedure

    You can call the FILEOPEN procedure to open a BFILE for read-only access. BFILEs may not be written through Oracle.

    Syntax

    PROCEDURE FILEOPEN (
          file_loc   IN OUT  BFILE, 
          open_mode  IN      BINARY_INTEGER := file_readonly); 
    

    Parameters

    Table 6-13 FILEOPEN Parameters
    Parameter Name   Meaning  

    file_loc  

    Locator for the BFILE.  

    open_mode  

    Open mode.  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    VALUE_ERROR exception is raised if file_loc or open_mode is NULL. INVALID_ARGVAL exception is raised if open_mode is not equal to FILE_READONLY. OPEN_TOOMANY if the number of open files in the session exceeds SESSION_MAX_OPEN_FILES, NOEXIST_DIRECTORY if the directory does not exist, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.

    Example

    PROCEDURE Example_10 IS
        fil BFILE;
    BEGIN
        -- open BFILE
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
        IF (DBMS_LOB.FILEEXISTS(fil))
        THEN
            DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
            -- file operation
            DBMS_LOB.FILECLOSE(fil);
        END IF;
        EXCEPTION
            WHEN some_exception
            THEN handle_exception;
    END;
    

    See Also:

    DBMS_LOB.FILECLOSE(), DBMS_LOB.FILECLOSEALL().

     

    DBMS_LOB.GETLENGTH() Function

    You can call the GETLENGTH() function to get the length of the specified LOB. The length in bytes or characters is returned. The length returned for a BFILE includes the EOF if it exists. Note that any 0-byte or space filler in the LOB caused by previous ERASE() or WRITE() operations is also included in the length count. The length of an empty internal LOB is 0.

    Syntax

    FUNCTION GETLENGTH (
      lob_loc    IN  BLOB) 
    RETURN INTEGER;
    
    FUNCTION GETLENGTH (
      lob_loc    IN  CLOB   CHARACTER SET ANY_CS) 
    RETURN INTEGER; 
    
    FUNCTION GETLENGTH (
      lob_loc    IN  BFILE) 
    RETURN INTEGER;
    

    Parameter

    Table 6-14 GETLENGTH Parameter
    Parameter Name   Meaning  

    lob_loc  

    The locator for the LOB whose length is to be returned.  

    Return Values

    The length of the LOB in bytes or characters as an INTEGER. NULL is returned if the input LOB is null. NULL is returned in the following cases for BFILEs:

    • lob_loc is NULL
    • lob_loc does not have the necessary directory and OS privileges
    • lob_loc cannot be read because of an OS read error

    Pragmas

    PRAGMA RESTRICT_REFERENCES(getlength, WNDS, WNPS, RNDS, RNPS);
    

    Exceptions

    None.

    Examples

    PROCEDURE Example_11a IS
        lobd        BLOB;
        length      INTEGER;
    BEGIN
        -- get the LOB locator
        SELECT b_lob INTO lobd FROM lob_table
            WHERE key_value = 42;
        length := DBMS_LOB.GETLENGTH(lob_loc);
        IF length IS NULL THEN
            DBMS_OUTPUT.PUT_LINE('LOB is null.');
        ELSE
            DBMS_OUTPUT.PUT_LINE('The length is '
                || length);
        END IF;
    END;
    PROCEDURE Example_11b IS
    DECLARE
        len INTEGER;
        fil BFILE;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12; 
        len := DBMS_LOB.LENGTH(fil);
    END;
    

    DBMS_LOB.INSTR() Function

    You can call the INSTR function to return the matching position of the Nth occurrence of the pattern in the LOB, starting from the offset you specify. For CLOBs, the VARCHAR2 buffer (the PATTERN parameter) and the LOB value must be from the same character set (single byte or fixed-width multibyte). For BFILEs, the file has to be already opened using a successful FILEOPEN() operation for this operation to succeed.

    Operations that accept RAW or VARCHAR2 parameters for pattern matching, such as INSTR, do not support regular expressions or special matching characters (as in the case of SQL LIKE) in the pattern parameter or substrings.

    Syntax

    FUNCTION INSTR (
      lob_loc    IN   BLOB,
      pattern    IN   RAW,
      offset     IN   INTEGER := 1,
      nth        IN   INTEGER := 1)
    RETURN INTEGER;
    
    FUNCTION INSTR (
      lob_loc    IN   CLOB      CHARACTER SET ANY_CS,
      pattern    IN   VARCHAR2  CHARACTER SET lob_loc%CHARSET,
      offset     IN   INTEGER := 1,
      nth        IN   INTEGER := 1)
    RETURN INTEGER;
    
    FUNCTION INSTR (
      lob_loc    IN   BFILE,
      pattern    IN   RAW,
      offset     IN   INTEGER := 1,
      nth        IN   INTEGER := 1)
    RETURN INTEGER;
    

    Parameters

    Table 6-15 INSTR Parameters
    Parameter Name   Meaning  

    lob_loc  

    The locator for the LOB to be examined.  

    pattern  

    The pattern to be tested for. The pattern is a group of RAW bytes for BLOBS, and a character string (VARCHAR2) for CLOBs.  

    offset  

    The absolute offset in bytes (BLOBs) or characters (CLOBs) at which the pattern matching is to start.  

    nth  

    The occurrence number, starting at 1.  

    Return Values

    INTEGER, offset of the start of the matched pattern, in bytes or characters. It returns 0 if the pattern is not found.

    A NULL is returned if:

    • any one or more of the IN parameters was null or invalid.
    • OFFSET < 1 or OFFSET > LOBMAXSIZE
    • nth < 1
    • nth > LOBMAXSIZE

    Pragmas

    PRAGMA RESTRICT_REFERENCES(instr, WNDS, WNPS, RNDS, RNPS);
    

    Exceptions

    For BFILEs, UNOPENED_FILE if the file was not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file.

    Examples

    PROCEDURE Example_12a IS
        lobd        CLOB;
        pattern     VARCHAR2 := 'abcde';
        position    INTEGER := 10000;
    BEGIN
    -- get the LOB locator
        SELECT b_col INTO lobd
            FROM lob_table
            WHERE key_value = 21;
        position := DBMS_LOB.INSTR(lobd,
                            pattern, 1025, 6);
        IF position = 0 THEN
            DBMS_OUTPUT.PUT_LINE('Pattern not found');
        ELSE
            DBMS_OUTPUT.PUT_LINE('The pattern occurs at ' 
                    || position);
        END IF;
    END;
    
    PROCEDURE Example_12b IS
    DECLAR    E
        fil BFILE;
        pattern VARCHAR2;
        pos INTEGER;
    BEGIN
        -- initialize pattern
        -- check for the 6th occurrence starting from 1025th byte
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
        DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
        pos := DBMS_LOB.INSTR(fil, pattern, 1025, 6);
        DBMS_LOB.FILECLOSE(fil);
    END;                            
    

    See Also:

    DBMS_LOB.SUBSTR()

     

    DBMS_LOB.LOADFROMFILE() Procedure

    You can call the LOADFROMFILE() procedure to copy all, or a part of, a source external LOB (BFILE) to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. Note that the amount and src_offset, since they refer to the BFILE, are in terms of bytes and the destination offset is either in bytes or characters for BLOBs and CLOBs respectively.


    Note:

    The input BFILE must have already been opened prior to using this procedure. Also, no character set conversions are performed implicitly when binary BFILE data is loaded into a CLOB. The BFILE data must already be in the same character set as the CLOB in the database. No error checking is performed to verify this.

     

    If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, existing data is overwritten.

    It is not an error to specify an amount that exceeds the length of the data in the source BFILE. Thus, you can specify a large amount to copy from the BFILE which will copy data from the src_offset to the end of the BFILE.

    Syntax

    PROCEDURE loadfromfile (
       dest_lob    IN OUT BLOB, 
       src_file    IN     BFILE, 
       amount      IN     INTEGER, 
       dest_offset IN     INTEGER := 1, 
       src_offset  IN     INTEGER  := 1); 
      
    PROCEDURE LOADFROMFILE(
       dest_lob    IN OUT CLOB CHARACTER SET ANY_CS, 
       src_file    IN     BFILE, 
       amount      IN     INTEGER, 
       dest_offset IN     INTEGER := 1, 
       src_offset  IN     INTEGER := 1); 
    

    Parameters

    Table 6-16 INSTR Parameters
    Parameter Name   Meaning  

    dest_lob  

    LOB locator of the target for the load.  

    src_file  

    BFILE locator of the source for the load.  

    amount  

    Number of bytes to load from the BFILE.  

    dest_offset  

    Offset in bytes or characters in the destination LOB (origin: 1) for the start of the load.  

    src_offset  

    Offset in bytes in the source BFILE (origin: 1) for the start

    of the load.  

    Return Values

    None

    Pragmas

    None.

    Exceptions

    VALUE_ERROR, if any of the input parameters are NULL or invalid.

    INVALID_ARGVAL, if

    Examples

    PROCEDURE Example_l2f IS 
      lobd       BLOB; 
      fils       BFILE := BFILENAME('SOME_DIR_OBJ','some_file'); 
      amt        INTEGER := 4000; 
    BEGIN 
      DBMS_LOB.FILEOPEN(fils, dbms_lob.file_readonly); 
      DBMS_LOB.LOADFROMFILE(lobd, fils, amt); 
      COMMIT; 
      DBMS_LOB.FILECLOSE(fils); 
    END; 
    

    DBMS_LOB.READ() Procedure

    You can call the READ() procedure to read a piece of a LOB, and return the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.

    The number of bytes or characters actually read is returned in the amount parameter. If the end of LOB value is reached during a READ(), amount will be set to 0, and a NO_DATA_FOUND exception will be raised.

    Syntax

    PROCEDURE READ (
      lob_loc   IN      BLOB,
      amount    IN OUT  BINARY_INTEGER,
      offset    IN      INTEGER,
      buffer    OUT     RAW);
    
    PROCEDURE READ (
      lob_loc   IN      CLOB     CHARACTER SET ANY_CS,
      amount    IN OUT  BINARY_INTEGER,
      offset    IN      INTEGER,
      buffer    OUT     VARCHAR2 CHARACTER SET lob_loc%CHARSET); 
    
    PROCEDURE READ (
      lob_loc   IN       BFILE,
      amount    IN OUT   BINARY_INTEGER,
      offset    IN       INTEGER,
      buffer    OUT      RAW);
    

    Parameters

    Table 6-17 READ Parameters
    Parameter Name   Meaning  

    lob_loc  

    The locator for the LOB to be read.  

    amount  

    The number of bytes or characters to be read.  

    offset  

    The offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1).  

    buffer  

    The output buffer for the read operation.  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    READ can raise any of the following exceptions:

    • VALUE_ERROR
      • any of lob_loc, amount, or offset parameters are null
    • INVALID_ARGVAL
      • AMOUNT < 1
      • AMOUNT > MAXBUFSIZE
      • OFFSET < 1
      • OFFSET > LOBMAXSIZE
      • AMOUNT is greater, in bytes or characters, than the capacity of BUFFER
    • NO_DATA_FOUND
      • the end of the LOB is reached and there are no more bytes or characters to read from the LOB. AMOUNT has a value of 0.
    • For BFILEs operations, UNOPENED_FILE if the file is not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file

    Examples

    PROCEDURE Example_13a IS
        src_lob        BLOB;
        buffer         RAW;
        amt            BINARY_INTEGER := 32767;
        pos            INTEGER := 2147483647;
    BEGIN
        SELECT b_col INTO src_lob
            FROM lob_table
            WHERE key_value = 21;
        LOOP
            DBMS_LOB.READ (src_lob, amt, pos, buffer);
            /* process the buffer */
            pos := pos + amt;
        END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('End of data');
    END;
    
    PROCEDURE Example_13b IS
        fil BFILE;
        buf RAW(32767);
        amt BINARY_INTEGER := 32767;
        pos INTEGER := 2147483647;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
        DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
        LOOP
            DBMS_LOB.READ(fil, amt, pos, buf);
            -- process contents of buf
            pos := pos + amt;
        END LOOP;
        EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          BEGIN
            DBMS_OUTPUT.PUTLINE (`End of LOB value reached');
            DBMS_LOB.FILECLOSE(fil);
          END;
    END;
    
    /* Example for efficient I/O on OS that performs */
    /* better with block I/O rather than stream I/O  */
    PROCEDURE Example_13c IS
        fil BFILE;
        amt BINARY_INTEGER := 1024; -- or n x 1024 for reading n 
        buf RAW(1024); -- blocks at a time
        tmpamt BINARY_INTEGER;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
        DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
        LOOP
            DBMS_LOB.READ(fil, amt, pos, buf);
            -- process contents of buf
            pos := pos + amt;
        END LOOP;
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                BEGIN
                    DBMS_OUTPUT.PUTLINE (`End of data reached');
                    DBMS_LOB.FILECLOSE(fil);
                END;
    END;
    

    DBMS_LOB.SUBSTR() Function

    You can call the SUBSTR() function to return amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

    For fixed-width n-byte CLOBs, if the input amount for SUBSTR() is specified to be greater than (32767/n), then SUBSTR() returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser.

    Syntax

    FUNCTION SUBSTR(
      lob_loc     IN    BLOB,
      amount      IN    INTEGER := 32767,
      offset      IN    INTEGER := 1)
    RETURN RAW;
    
    FUNCTION SUBSTR(
      lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
      amount      IN    INTEGER := 32767,
      offset      IN    INTEGER := 1)
    RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;
    
    FUNCTION SUBSTR(
      lob_loc     IN    BFILE,
      amount      IN    INTEGER := 32767,
      offset      IN    INTEGER := 1)
    RETURN RAW;
    

    Parameters

    Table 6-18 SUBSTR Parameters
    Parameter Name   Meaning  

    lob_loc  

    The locator for the LOB to be read.  

    amount  

    The number of bytes or characters to be read.  

    offset  

    The offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1).  

    Return Values

    RAW, for the function overloading that has a BLOB or BFILE in parameter.

    VARCHAR2, for the CLOB version.

    NULL, if:

    • any input parameter is null
    • AMOUNT < 1
    • AMOUNT > 32767
    • OFFSET < 1
    • OFFSET > LOBMAXSIZE

    Pragmas

    PRAGMA RESTRICT_REFERENCES(substr, WNDS, WNPS, RNDS, RNPS);
    

    Exceptions

    For BFILE operations, UNOPENED_FILE if the file is not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file

    Example

    PROCEDURE Example_14a IS
        src_lob        CLOB;
        pos            INTEGER := 2147483647;
        buf            VARCHAR2(32000);
    BEGIN
        SELECT c_lob INTO src_lob FROM lob_table
            WHERE key_value = 21;
        buf := DBMS_LOB.SUBSTR(src_lob, 32767, pos);
        /* process the data */
    END;
    
    PROCEDURE Example_14b IS
        fil BFILE;
        pos INTEGER := 2147483647;
        pattern RAW;
    BEGIN
        SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
        DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
        pattern := DBMS_LOB.SUBSTR(fil, 255, pos);
        DBMS_LOB.FILECLOSE(fil);
    END;    
    

    See Also:

    DBMS_LOB.INSTR(), DBMS_LOB.READ()

     

    DBMS_LOB.TRIM() Procedure

    You can call the TRIM() procedure to trim the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and in characters for CLOBs.

    If you attempt to TRIM() an empty LOB, nothing occurs, and TRIM() returns no error. If the new length that you specify in newlen is greater than the size of the LOB, an exception is raised.

    Syntax

    FUNCTION TRIM (
        lob_loc        IN    BLOB,
        newlen         IN    INTEGER);
    
    FUNCTION TRIM (
        lob_loc        IN    CLOB,
        newlen         IN    INTEGER):
    

    Parameters

    Table 6-19 TRIM Parameters
    Parameter Name   Meaning  

    lob_loc  

    The locator for the internal LOB whose length is to be trimmed.  

    newlen  

    The new, trimmed length of the LOB value in bytes for BLOBs or characters for CLOBs.  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

    VALUE_ERROR, if lob_loc is null.

    INVALID_ARGVAL, if

    • NEW_LEN < 0
    • NEW_LEN > LOBMAXSIZE

    Example

    PROCEDURE Example_15 IS
        lob_loc        BLOB;
    BEGIN
    -- get the LOB locator
        SELECT b_col INTO lob_loc
            FROM lob_table
            WHERE key_value = 42 FOR UPDATE;
        DBMS_LOB.TRIM(lob_loc, 4000);
        COMMIT;
    END;
    

    See Also:

    DBMS_LOB.ERASE()

     

    \DBMS_LOB.WRITE() Procedure

    You can call the WRITE() procedure to write a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.

    WRITE() replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.

    It is an error if the input amount is more than the data in the buffer. If the input amount is less than the data in the buffer, only amount bytes/characters from the buffer is written to the LOB. If the offset you specify is beyond the end of the data currently in the LOB, zero-byte fillers or spaces are inserted in the BLOB or CLOB respectively.

    Syntax

    PROCEDURE WRITE (
      lob_loc  IN OUT  BLOB,
      amount   IN      BINARY_INTEGER,
      offset   IN      INTEGER,
      buffer   IN      RAW);
    
    PROCEDURE WRITE (
      lob_loc  IN OUT  CLOB   CHARACTER SET ANY_CS,
      amount   IN      BINARY_INTEGER,
      offset   IN      INTEGER,
      buffer   IN      VARCHAR2 CHARACTER SET lob_loc%CHARSET); 
    

    Parameters

    Table 6-20 WRITE Parameters
    Parameter Name   Meaning  

    lob_loc  

    The locator for the internal LOB to be written to.  

    amount  

    The number of bytes or characters to write, or that were written.  

    offset  

    The offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1) for the write operation.  

    buffer  

    The input buffer for the write.  

    Return Values

    None.

    Pragmas

    None.

    Exceptions

      Example

      PROCEDURE Example_16 IS
          lob_loc        BLOB;
          buffer         RAW;
          amt            BINARY_INTEGER := 32767;
          pos            INTEGER := 2147483647;
          i              INTEGER;
      BEGIN
          SELECT b_col INTO lob_loc
              FROM lob_table
              WHERE key_value = 12;
          FOR i IN 1..3 LOOP
              DBMS_LOB.WRITE (lob_loc, amt, pos, buffer);
              /* fill in more data */
              pos := pos + amt;
          END LOOP;
          EXCEPTION4
              WHEN some_exception
              THEN handle_exception;
      END;
      

      See Also:

      DBMS_LOB.APPEND(), DBMS_LOB.COPY().

       

      LOB Restrictions

      The use of LOBs are subject to some restrictions:

      Also, LOBs are not allowed in the following places:




    Prev

    Next
    Oracle
    Copyright © 1997 Oracle Corporation.

    All Rights Reserved.

    Library

    Product

    Contents

    Index