Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 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 - introduces LOBs in Oracle8, and discusses the main aspects of LOB handling
- LOB Reference - contains the detailed list of all of the technical specifications for the PL/SQL `DBMS_LOB` package

## Introduction to LOBs

#### Introduction Overview

This section introduces the treatment of `LOB`s 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:

- What are `LOB`s?
- Internal `LOB`s and External `LOB`s (`BFILE`s)
  - Internal `LOB`s
  - External `LOB`s (`BFILE`s)
- `LOB` Datatypes
  - Internal `LOB` datatypes
  - External `LOB` datatypes
- `LOB`s in comparison to `LONG` and `LONG` `RAW` types
- Packages for Working with `LOB`s

The second topic discusses steps involved in beginning to work with `LOB`s:

- Defining Internal and External `LOB`s for Tables (SQL DDL)
- Stipulating Tablespace and Storage Characteristics for Internal `LOB`s
  - Tablespace and `LOB` Index
  - `PCTVERSION`
  - `CACHE` / `NO` `CACHE`
  - `LOGGING` / `NO` `LOGGING`
  - `CHUNK`
- Initializing Internal `LOB`s (SQL DML)

The third topic deals with issues specific to handling external `LOB`s (`BFILE`s):

- Accessing External `LOB`s (SQL DML)
  - Initializing `BFILES` using `BFILENAME`() and `OCIFileSetName`()
  - The `DIRECTORY` object
  - `DIRECTORY` Name Specification
- `BFILE` Security
  - Ownership and security
  - SQL DDL for `BFILE` security
  - SQL DML for `BFILE` security
- Catalog Views on Directories
- Guidelines for `DIRECTORY` Usage
- Maximum number of open `BFILES`
- `BFILE`s in MTS mode
- Closing `BFILE`s after Program Termination

The fourth topic considers how `LOB`s are handled by way of locators:

- `LOB` Values and Locators
  - Inline storage of `LOB` values
  - `LOB` locators
  - Internal `LOB` locators
  - `BFILE` locators
- `LOB` Locator Operations
  - Setting the `LOB` Column/Attribute to contain a Locator
  - Accessing a `LOB` through a locator
  - Read consistent locators
  - Updated locators
  - `LOB` bind variables

The fifth topic is concerned with basic manipulation of `LOB`s:

- Efficient reads and writes of large amounts of `LOB` data
- Copying `LOB`s
  - Copying internal `LOB`s
  - Copying external `LOB`s
- Deleting `LOB`s
  - Deleting internal `LOB`s
  - Deleting external `LOB`s
- Copying Data from `LONG`s to `LOB`s

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

- `LOB`s in the Object Cache
- The `LOB` Buffering Subsystem
- User Guidelines for Best Performance Practices
- Working with Varying-Width Character Data

### 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 `LOB`s - 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 `LOB`s as being of two kinds depending on their location with regard to the database - internal `LOB`s and external `LOB`s (`BFILEs`). When the term `LOB` is used without an identifying prefix term, it refers to both internal and external `LOB`s. Data stored in a LOB is termed the LOB's value.

### Internal LOBs and External LOBs (BFILEs)

#### Internal LOBs

Internal `LOB`s, as their name suggests, are stored in the database tablespaces in a way that optimizes space and provides efficient access. Internal `LOB`s use copy semantics and participate in the transactional model of the server. Internal `LOB`s 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 `LOB`s also. This means that any changes to a internal `LOB` value can be committed or rolled back.

#### External LOBs (BFILEs)

External `LOB`s, 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:

- Multiple `LOB`s are allowed in a single row.
- `LOB`s can be attributes of a user-defined datatype (object).
- Only the LOB locator is stored in the table column; `BLOB` and `CLOB` data can be stored in separate tablespaces and `BFILE` data is stored as an external file.
- When you access a `LOB` column, it is the locator which is returned.
- A LOB can be up to 4 gigabytes in size. The `BFILE` maximum is operating system dependent, but cannot exceed 4 gigabytes. The valid accessible range is 1 to (232-1).
- `LOB`s let you access and manipulate data in a random, piece-wise manner.

### Packages for Working with LOBs

You can make changes to the entire values of internal `LOB`s 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 `LOB`s for read purposes and also write to internal `LOB`s.

- 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.
- The PL/SQL DBMS_LOB API is described in "DBMS_LOB Package" on page 6-68.

### LOB Datatypes

#### Internal LOB Datatypes

There are three SQL datatypes for defining instances of internal `LOB`s:

- `BLOB`, a `LOB` whose value is composed of unstructured binary ("raw") data.
- `CLOB`, a `LOB` whose value is composed of single-byte fixed-width character data that corresponds to the database character set defined for the Oracle8 database.
- `NCLOB`, a `LOB` whose value is composed of fixed-width multi-byte character data that corresponds to the national character set defined for the Oracle8 database.

Varying width character data is not supported for `BLOB`s, `CLOB`s and `NCLOB`s.

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

#### External LOB Datatype

There is one external SQL `LOB` datatype:

- `BFILE`, a `LOB` whose value is composed of binary ("raw") data, and is stored outside of the database tablespaces in a server-side operating system file.

### Defining Internal and External LOBs for Tables

It is possible to incorporate `LOB`s into tables in two ways.

- `LOB`s may be columns in a table - the case in which the large object is 'in relation' with other data entities.
- `LOB`s may be attributes of an object - the case in which a data entity (i.e. an object type) has one or more `LOB`s as attributes.

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 `LOB`s in the following DDL commands:

- `CREATE` `TABLE` and `ALTER` `TABLE`
  - `BLOB`, `CLOB`, `NCLOB` and `BFILE` columns
  - `LOB` storage clause for internal `LOB` columns/attributes
- `CREATE` `TYPE` and `ALTER` `TYPE`
  - `BLOB`, `CLOB`, and `BFILE` attributes (noting that `NCLOB`s cannot be attributes in an object type).

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 `LOB`s 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 `LOB`s 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 `LOB`s can be achieved by specifying storage for `LOB`s in a tablespace that is different from the one used for the table that contains the `LOB`. If many different `LOB`s 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:

- If you do not specify a tablespace for the `LOB` data nor for the `LOB` index, the table's tablespace is used for both the `LOB` data and the `LOB` index.
- If you specify a tablespace for the `LOB` data but not for the `LOB` index, both the `LOB` data and index use the tablespace that was specified for the `LOB` data.
- If you specify a tablespace for the `LOB` index but not the `LOB` data, the `LOB` index uses the specified tablespace and the `LOB` data uses the table's tablespace.
- If you specify a tablespace for the `LOB` data and the `LOB` index, the `LOB` data and index use the specified tablespaces respectively.

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 `LOB`s updated at any given point in time * % of each `LOB` updated whenever a `LOB` is updated * % of `LOB`s 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 `LOB`s, 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:

`LOB`s 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 `LOB`s 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 `LOB`s 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 `LOB`s, there is a further alternative depending on how `CACHE` is stipulated.

- If the [`NO`]`LOGGING` clause is omitted and `CACHE` is specified, `LOGGING` is automatically implemented (because you cannot have `CACHE` `NOLOGGING`).
- If the [`NO`]`LOGGING` clause is omitted and `CACHE` is not specified, the process defaults in the same way as it does for tables and partitioned tables i.e.,the [`NO`]`LOGGING` value is obtained from the tablespace in which the `LOB` value resides.

The following issues should also be kept in mind.

- `LOB`s will always generate undo for `LOB` index pages. Regardless of whether `LOGGING` or `NOLOGGING` is set `LOB`s will never generate rollback information (undo) for `LOB` data pages because old `LOB` data is stored in versions. Rollback information that is created for `LOB`s tends to be small because it is only for the `LOB` index page changes.
- When `LOGGING` is set Oracle will generate full redo for `LOB` data pages. `NOLOGGING` is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, you will not be able to recover your changes from the log since the changes were never logged.

  An example of when `NOLOGGING` is useful is bulk loads or inserts. For instance, when loading data into the `LOB`, if you don't care about redo and can just start the load over if it fails, set the `LOB`'s data segment storage characteristics to `NOCACHE` `NOLOGGING`. This will give good performance for the initial load of data. Once you have completed loading the data, you can use `ALTER` `TABLE` to modify the `LOB` storage characteristics for the `LOB` data segment to be what you really want for normal `LOB` operations -- i.e. `CACHE` or `NOCACHE` `LOGGING`.

  Note: `CACHE` implies that you also get `LOGGING`.

#### 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:

- `CHUNK` <= `NEXT`

and

- `CHUNK` <= `INITIAL`

#### 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 `LOB`s 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 `LOB`s 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 `LOB`s or to a filename for external `LOB`s. 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 `BFILE`s 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:

- SQL DDL to `CREATE` and `REPLACE`/`ALTER` a `DIRECTORY` object.
- SQL DML to `GRANT` and `REVOKE` the `READ` system and object privileges on `DIRECTORY` objects.

#### 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:

- `CREATE` `ANY` `DIRECTORY` - for creating or altering the directory object creation
- `DROP` `ANY` `DIRECTORY` - for deleting the directory object

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:

- `CREATE` `DIRECTORY`
- `DROP` `DIRECTORY`

#### SQL DML for BFILE security

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

- `GRANT` (system privilege)
- `GRANT` (object privilege)
- `REVOKE` (system privilege)
- `REVOKE` (object privilege)
- `AUDIT` (new statements)
- `AUDIT` (schema objects)

### 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:

- `ALL_DIRECTORIES` (`OWNER`, `DIRECTORY_NAME`, `DIRECTORY_PATH`)

  This view describes all the directories accessible to the user.

- `DBA_DIRECTORIES`(`OWNER`, `DIRECTORY_NAME`, `DIRECTORY_PATH`)

  This view describes all the directories specified for the entire database.

### 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:

- A `DIRECTORY` should not be mapped to physical directories which contain Oracle datafiles, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could potentially corrupt the database or the server operating system.
- The system privileges such as `CREATE` `ANY` `DIRECTORY` (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.
- Privileges on directory objects should be granted to different users carefully. The same holds for the use of the `WITH` `GRANT` `OPTION` clause when granting privileges to users.
- `DIRECTORY` objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, `DBMS_LOB` or OCI operations from all sessions on all files associated with this directory object will fail. Further, if a `DROP` or `REPLACE` command is executed before these files could be successfully closed, the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shutdown.

  The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls `DBMS_LOB` `FILECLOSEALL`() and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.

  See Also: "DBMS_LOB.FILECLOSEALL() Procedure" on page 6-84

- Similarly, revoking an user's privilege on a directory using the `REVOKE` statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or execute a `FILECLOSEALL`() in the session and restart the file operations.

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 `BFILE`s 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 `BFILE`s in MTS mode. This implies that operations on open `BFILE`s 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 `LOB`s are intended to be large objects, inline storage will only be relevant if your application mixes 'small' and 'large' `LOB`s.

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 `LOB`s, 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 `BFILE`s, 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.

- For internal `LOB`s, you can accomplish this by initializing the internal `LOB` to empty in an `INSERT`/`UPDATE` statement using the functions `EMPTY_BLOB`() for `BLOB`s or `EMPTY_CLOB`() for `CLOB`s and `NCLOB`s.

  See Also: "EMPTY_BLOB() and EMPTY_CLOB() Functions" on page 6-60.

- For external `LOB`s, you can initialize the `BFILE` column to point to an external file by using the `BFILENAME`() function.

  See Also: "BFILENAME() Function" on page 6-62.

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

- sets b_lob to `NULL`,
- populates c_lob with the character string 'abcde',
- sets `n_lob` to `NULL`, and
- initializes f_lob to point to the file 'scott.dat' located under the logical directory '`SCOTT_DIR`' (see the `CREATE` `DIRECTORY` command in the Oracle8 Reference). Character strings are inserted using the default character set for the instance.

    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 `LOB`s 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 `SELECT`ed 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 `CLOB`s are created as potential locators: `clob_selected`, `clob_updated` and `clob_copied`.

- At the time of the first `SELECT` `INTO` (at t1), the value in c_lob is associated with the locator clob_selected.
- In the second operation (at t2), the value in c_lob is associated with the locator clob_updated. Since there has been no change in the value of c_lob between t1 and t2, both clob_selected and clob_updated are read consistent locators that effectively have the same value even though they reflect snapshots taken at different moments in time.
- The third operation (at t3) copies the value in clob_selected to clob_copied. At this juncture, all three locators see the same value. The example demonstrates this with a series of `dbms_lob.read` calls.
- At this juncture (at t4), the program utilizes `dbms_lob.write` to alter the value in`` clob_updated, and a `dbms_lob.read` reveals a new value.
- However, a `dbms_lob.read` of the value through clob_selected (at t5) reveals that it is a read consistent locator, continuing to refer to the same value as of the time of its `SELECT`.
- Likewise, a `dbms_lob.read` of the value through clob_copied (at t6) reveals that it is a read consistent locator, continuing to refer to the same value as clob_selected.

##### 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`.

- At the time of the first `SELECT` `INTO` (at t1), the value in c_lob is associated with the locator clob_selected.
- In the second operation (at t2), the value in c_lob is modified through the `SQL` `UPDATE` command, bypassing the clob_selected locator. The locator still sees the value of the `LOB` as of the point in time of the original `SELECT`. In other words, the locator does not see the update made via the SQL `UPDATE` command. This is illustrated by the subsequent `dbms_lob.read` call.
- The third operation (at t3) re-selects the `LOB` value into the locator clob_selected. The locator is thus updated with the latest snapshot environment which allows the locator to see the change made by the previous SQL `UPDATE` command. Therefore, in the next `dbms_lob.read`, an error is returned because the `LOB` value is empty (i.e., it does not contain any data).

##### 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 `CLOB`s are created as potential locators: `clob_updated` and `clob_copied`.

- At the time of the first `SELECT` `INTO` (at t1), the value in c_lob is associated with the locator clob_updated.
- The second operation (at t2) copies the value in clob_updated to clob_copied. At this juncture, both locators see the same value. The example demonstrates this with a series of `dbms_lob.read` calls.
- At this juncture (at t3), the program utilizes `dbms_lob.write` to alter the value in`` clob_updated, and a `dbms_lob.read` reveals a new value.
- However, a `dbms_lob.read` of the value through clob_copied (at t4) reveals that it still sees the value of the `LOB` as of the point in time of the assignment from clob_updated (at t2).
- It is not until clob_updated is assigned to clob_copied (t5) that clob_copied sees the modification made by clob_updated.

##### 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 `CLOB`s are created as potential locators: `clob_selected`, `clob_updated` and `clob_copied`.

- At the time of the first `SELECT` `INTO` (at t1), the value in c_lob is associated with the locator clob_updated.
- The second operation (at t2) copies the value in clob_updated to clob_copied. At this juncture, both locators see the same value.
- Then (at t3), the program utilizes `dbms_lob.write` to alter the value in`` clob_updated, and a `dbms_lob.read` reveals a new value.
- However, a `dbms_lob.read` of the value through clob_copied (at t4) reveals that clob_copied does not see the change made by clob_updated.
- Therefore (at t5), when clob_copied is used as the source for the value of the `INSERT` statement, we insert the value associated with clob_copied (i.e. without the new changes made by clob_updated). This is demonstrated by the subsequent `dbms_lob.read` of the value just inserted.

##### 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`.

- At the time of the first `SELECT` `INTO` (at t1), the value in c_lob is associated with the locator clob_updated.
- The second operation (at t2), utilizes the `dbms_lob.write` command to alter the value in`` clob_updated, and a `dbms_lob.read` reveals a new value.
- The `commit` statement (at t3) ends the current transaction.
- Therefore (at t4), the subsequent `dbms_lob.read` operation fails because the clob_updated locator refers to a different (already committed) transaction. This is noted by the error returned. You must re-select the `LOB` locator before using it in further `dbms_lob` (and OCI) operations.

##### 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 `BFILE`s. 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 `LOB`s 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:

- Buffering enables deferred writes to the server. You can buffer up several writes in the `LOB`'s buffer in the client's address space and eventually flush the buffer to the server. This reduces the number of network roundtrips from your client application to the server, and hence, makes for better overall performance for `LOB` updates.
- Buffering reduces the overall number of `LOB` updates on the server, thereby reducing the number of `LOB` versions and amount of logging. This results in better overall `LOB` performance and disk space usage.

#### Considerations in the Use of LOB Buffering

The following caveats hold for buffered `LOB` operations:

- Oracle8 provides a simple buffering subsystem, and not a cache. To be specific, Oracle8 does not guarantee that the contents of a `LOB`'s buffer are always in synch with the `LOB` value in the server. Unless you explicitly flush the contents of a `LOB`'s buffer, you will not see the results of your buffered writes reflected in the actual `LOB` on the server.
- Error recovery for buffered `LOB` operations is your responsibility. Owing to the deferred nature of the actual `LOB` update, error reporting for a particular buffered read or write operation is deferred until the next access to the server based `LOB`.
- Transactions involving buffered `LOB` operations cannot migrate across user sessions - LBS is a single user, single threaded system.
- Oracle8 does not guarantee transactional support for buffered `LOB` operations. To ensure transactional semantics for buffered `LOB` updates, you must maintain logical savepoints in your application to rollback all the changes made to the buffered `LOB` in the event of an error. You should always wrap your buffered `LOB` updates within a logical savepoint.
- In any given transaction, once you have begun updating a `LOB` using buffered writes, it is your responsibility to ensure that the same `LOB` is not updated through any other operation within the scope of the same transaction that bypasses the buffering subsystem.

  You could potentially do this by using an SQL statement to update the server-based `LOB`. Oracle8 cannot distinguish, and hence prevent, such an operation. This will seriously affect the correctness and integrity of your application.

- Buffered operations on a `LOB` are done through its locator, just as in the conventional case. A locator that is enabled for buffering will provide a consistent read version of the `LOB`, until you perform a write operation on the `LOB` through that locator.

  See Also: "Read consistent locators" on page 6-24.

  Once the locator becomes an updated locator by virtue of its being used for a buffered write, it will always provide access to the most up-to-date version of the `LOB` as seen through the buffering subsystem. Buffering also imposes an additional significance to this updated locator - all further buffered writes to the `LOB` can be done only through this updated locator. Oracle8 will return an error if you attempt to write to the `LOB` through other locators enabled for buffering.

  See Also: "Updated locators" on page 6-28.

- You can pass an updated locator that was enabled for buffering as an `IN` parameter to a PL/SQL procedure. However, passing an `IN` `OUT` or an `OUT` parameter will produce an error, as will an attempt to return an updated locator.
- You cannot assign an updated locator that was enabled for buffering to another locator. There are a number of different ways that assignment of locators may occur - through `OCILobAssign`(), through assignment of PL/SQL variables, through `OCIObjectCopy`() where the object contains the `LOB` attribute, and so on. Assigning a consistent read locator that was enabled for buffering to a locator that did not have buffering enabled, turns buffering on for the target locator. By the same token, assigning a locator that was not enabled for buffering to a locator that did have buffering enabled, turns buffering off for the target locator.

  Similarly, if you `SELECT` into a locator for which buffering was originally enabled, the locator becomes overwritten with the new locator value, thereby turning buffering off.

- Appending to the `LOB` value using buffered write(s) is allowed, but only if the starting offset of these write(s) is exactly one byte (or character) past the end of the `BLOB` (or `CLOB`/`NCLOB`). In other words, the buffering subsystem does not support appends that involve creation of zero-byte fillers or spaces in the server based `LOB`.
- For `CLOB`s, Oracle8 requires that the character set form for the locator bind variable on the client side be the same as that of the `LOB` in the server. This is usually the case in most OCI `LOB` programs. The exception is when the locator is `SELECT`ed from a remote database, which may have a different character set form from the database which is currently being accessed by the `OCI` program. In such a case, an error is returned. If there is no character set form input by the user, then we assume it is `SQLCS_IMPLICIT`.

#### 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 `LOB`s 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 `LOB`s 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 `LOB`s, 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 `LOB`s 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

- write the dirty pages in the `LOB`'s buffer to the server-based `LOB`, thereby updating the `LOB` value,
- reset the updated locator to be a read consistent locator, and
- either free the flushed buffers or turn the status of the buffer pages back from dirty to unmodified.

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:

- before committing the transaction,
- before migrating from the current transaction to another,
- before disabling buffering operations on a `LOB`
- before returning from an external callout execution into the calling function/procedure/method in PL/SQL.

  Note: When the external callout is called from a PL/SQL block and the locator is passed as a parameter, all buffering operations, including the enable call, should be made within the callout itself. In other words, we recommend that you adhere to the following sequence: call the external callout, enable the locator for buffering, read/write using the locator, flush the `LOB`, disable the locator for buffering, and return to the calling function/procedure/method in PL/SQL. Remember that Oracle8 never implicitly flushes the `LOB`.

##### 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.

- A locator that is enabled for buffering can only be used with the following OCI APIs:

  `OCILobRead`()`, OCILobWrite`()`, OCILobAssign`()`, OCILobIsEqual`()`, OCILobLocatorIsInit`()`, OCILobLocatorSize`()`, OCILobCharSetId`()`, OCILobCharSetForm`().

- The following OCI APIs will return errors if used with a locator enabled for buffering:

  `OCILobCopy`()`, OCILobAppend`()`, OCILobErase`()`, OCILobGetLength`()`, OCILobTrim`()`.`

  These APIs will also return errors when used with a locator which has not been enabled for buffering, but the `LOB` that the locator represents is already being accessed in buffered mode through some other locator.

- An error is returned from `DBMS_LOB` APIs if the input lob locator has buffering enabled.
- As in the case of all other locators, locators enabled for `LOB` buffering cannot span transactions.

##### 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

- Since `LOB`s are big, you can obtain the best performance by reading and writing large chunks of a `LOB` value at a time. This helps in several respects:
  1. If accessing the `LOB` from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
  2. If using the '`NOCACHE`' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
  3. Writing to the `LOB` creates a new version of the `LOB` `CHUNK`. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, the `CHUNK` is also stored in the redo log.
- If you need to read/write small pieces of `LOB` data on the client, use `LOB` buffering - see `OCILobEnableBuffering`()`, OCILobDisableBuffering`()`, OCILobFlushBuffer`()`, OCILobWrite`()`, OCILobRead`()`.` Basically, turn on `LOB` buffering before reading/writing small pieces of `LOB` data.

  See Also: "LOB Buffering Subsystem" on page 6-47 for more information on `LOB` buffering.

- Use `OCILobWrite`() and `OCILobRead`() with a callback so data is streamed to/from the `LOB`. Make sure that the length of the entire write is set in the 'amount' parameter on input.
- Use a checkout/checkin model for `LOB`s. `LOB`s are optimized for the following:
  1. SQL `UPDATE` which replaces the entire `LOB` value
  2. Copy the entire `LOB` data to the client, modify the `LOB` data on the client side, copy the entire `LOB` data back to the database. This can be done using OCILobRead() and OCILobWrite() with streaming.

### Working with Varying-Width Character Data

Varying width character data is not supported for `BLOB`s, `CLOB`s and `NCLOB`s. However, `BLOB`s can contain any data. Since `CLOB`s/`NCLOB`s 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.

`BFILE`s 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.

- If a user other than the system user tries to create a table with a `CLOB` column, the create will fail.
- If the system user tries to create a table with a `CLOB` column, the create will succeed. However, subsequent inserts into the table will fail if the `CLOB` column has a value other than `NULL`.

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.

- SQL DML functions `EMPTY_BLOB`(), `EMPTY_CLOB`() and `BFILENAME`() which are used for initialization (immediately following this overview).
- Summary of means provided by the OCI for manipulating `LOB`s (beginning with "Using the OCI to Manipulate LOBs" on page 6-64).
- The `DBMS_LOB` package, listing all functions and procedures (beginning with "DBMS_LOB Package" on page 6-68). This section contains the main body of technical specifications that underlie `LOB`s.
- A brief list of constraints that apply to using `LOB`s at the time of the first production release of Oracle8.

### 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 `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s. 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 `BLOB`s, `CLOB`s, `NCLOB`s, and `BFILE`s. You can use `DBMS_LOB` for access and manipulation of specific parts of a `LOB`, as well as complete `LOB`s. `DBMS_LOB` can read as well as modify `BLOB`s, `CLOB`s, and `NCLOB`s, and provides read-only operations on `BFILE`s.

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 `LOB`s, 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 `LOB`s, 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 `LOB`s 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:

- `APPEND`() - append the contents of the source `LOB` to the destination `LOB`
- `COPY`() - copy all or part of the source `LOB` to the destination `LOB`
- `ERASE`() - erase all or part of a `LOB`
- `LOADFROMFILE`() - load `BFILE` data into an internal `LOB`
- `TRIM`() - trim the `LOB` value to the specified shorter length
- `WRITE`()- write data to the `LOB` from a specified offset

The routines that read or examine `LOB` values are:

- `GETLENGTH`() - get the length of the `LOB` value
- `INSTR`() - return the matching position of the nth occurrence of the pattern in the `LOB`
- `READ`() - read data from the `LOB` starting at the specified offset
- SUBSTR() - return part of the LOB value starting at the specified offset

The read-only routines specific to `BFILE`s are:

- `FILECLOSE`() - close the file
- `FILECLOSEALL`()- close all previously opened files
- `FILEEXISTS`() - check if the file exists on the server
- `FILEGETNAME`() - get the directory alias and file name
- `FILEISOPEN`() - check if the file was opened using the input `BFILE`
- locators
- `FILEOPEN`() - open a file

### Datatypes

Parameters for the `DBMS_LOB` routines use the datatypes:

- `BLOB`, for a source or destination binary `LOB`
- `RAW`, for a source or destination raw buffer (used with `BLOB`)
- `CLOB`, for a source or destination character `LOB` (including `NCLOB`)
- `VARCHAR`2, for a source or destination character buffer (used with `CLOB` and `NCLOB`)
- `INTEGER`, to specify the size of a buffer or `LOB`, the offset into a `LOB`, or the amount to access

### Type Definitions

The `DBMS_LOB` package defines no special types. `NCLOB` is a special case of `CLOB`s for fixed-width, multi-byte national character sets. The clause '`ANY_CS`' in the specification of `DBMS_LOB` routines for `CLOB`s 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 `LOB`s (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 `BFILE`s 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 `BLOB`s and `BFILES`, and characters for `CLOB`s and `NCLOB`s.
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 `CLOB`s and `NCLOB`s.

  - You must ensure that the character set of the `VARCHAR2` buffer in a `DBMS_LOB` routine for `CLOB`s 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 `BFILE`s 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 `LOB`s, or parts of two `LOB`s. You can only compare `LOB`s of the same datatype. That is, you compare `LOB`s of `BLOB` type with other `BLOB`s, and `CLOB`s with `CLOB`s, and `BFILE`s with `BFILE`s. For `BFILE`s, 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 `CLOB`s, 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

- `INTEGER` -- zero if the comparison succeeds, non-zero if not.
- `NULL`, if
  - amount < 1
  - amount > `LOBMAXSIZE`
  - offset_1 or offset_2 < 1
  - offset_1 or offset_2 > `LOBMAXSIZE`

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 `LOB`s, 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 `BLOB`s or `CLOB`s 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 `BLOB`s) or characters (for `CLOB`s) to be erased.
`offset` | Absolute offset from the beginning of the `LOB` in bytes (for `BLOB`s) or characters (`CLOB`s).

Return Values

None.

Pragmas

None.

Exceptions

`VALUE_ERROR`, if any input parameter is `NULL`.

`INVALID_ARGVAL`, if

- `AMOUNT` < 1 or `AMOUNT` > `LOBMAXSIZE`
- `OFFSET` < 1 or `OFFSET` > `LOBMAXSIZE`

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 `BFILE`s. This means that `BFILE`s 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 `BFILE`s 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. `BFILE`s 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
--- | ---
l`ob_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 `BFILE`s:

  - 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 `CLOB`s, the `VARCHAR2` buffer (the `PATTERN` parameter) and the `LOB` value must be from the same character set (single byte or fixed-width multibyte). For `BFILE`s, 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 `BFILE`s, `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 `LOB`s, 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 `BLOB`s and `CLOB`s 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

- src_offset or dest_offset < 1
- src_offset or dest_offset > `LOBMAXSIZE`
- amount < 1
- amount > `LOBMAXSIZE`

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 `BFILE`s 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 `CLOB`s, 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 `BLOB`s) or characters (for `CLOB`s) 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 `BLOB`s, and in characters for `CLOB`s.

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 `BLOB`s or characters for `CLOB`s.

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 `BLOB`s) or characters (for `CLOB`s) 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

- `VALUE_ERROR`
  - if any of `LOB_LOC`, `AMOUNT`, or `OFFSET` parameters are null, out of range, or invalid
- `INVALID_ARGVAL`
  - AMOUNT < 1
  - AMOUNT > MAXBUFSIZE
  - OFFSET < 1
  - OFFSET > LOBMAXSIZE

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 `LOB`s are subject to some restrictions:

- `LOB`s must be stored in tables -- they cannot be transient/temporary.
- A `LONG` datatype may not be converted nor migrated to a `LOB` datatype and vice versa.

  A workaround is to do the following:

  1. Write the data in the long `RAW` to a server side file.
  2. Use the Oracle8 command `CREATE` `DIRECTORY` to point to the directory where the file was written.
  3. Use the Oracle8 command `OCILobLoadFromFile`() or `DBMS_LOB.LOADFROMFILE()` to populate the `LOB` with the data in the file.

  If the `LONG` isn't too big, another way is to read the `LONG` into a buffer and call `OCILobWrite` or `DBMS_LOB`.`WRITE`() to write the `LONG` data to the `LOB`.

  In either case, you'll need to either add a `LOB` column to the original table or create a new table that contains the `LOB` column. Oracle8 does not allow changing the datatype of a column to a `LOB` type.

- Distributed `LOB`s are not supported. Specifically, this means that the user cannot use a remote locator in the `SELECT` and `WHERE` clauses. This includes using `DBMS_LOB` package functions. In addition, references to objects in remote tables with or without `LOB` attributes is not allowed.

  For example, the following operations are invalid:

  - `SELECT` lobcol from table1@remote_site;
  - `INSERT` `INTO` lobtable select type1.lobattr from table1@remote_site;
  - `SELECT` dbms_lob.length(lobcol) from table1@remote_site;

  Valid operations on `LOB` columns in remote tables include:

  - `CREATE` `TABLE` as select * from table1@remote_site;
  - `INSERT` `INTO` t select * from table1@remote_site;
  - `UPDATE` t set lobcol = (select lobcol from table1@remote_site);
  - `INSERT` `INTO` table1@remote...
  - `UPDATE` table1@remote...
  - `DELETE` table1@remote...

- There is no loader (direct or conventional path) support for `LOB`s. Instead, use `OCILobLoadFromFile`(), `DBMS_LOB`.`LOADFROMFILE`(), or `OCILobWrite`() with streaming.
- When binding an internal `LOB` in order to use piece-wise `INSERT`/`UPDATE`, the bind variable may be of type `SQLT_CHR` or `SQLT_LBI` but is limited to 4k. You cannot bind a `SQLT_LNG` to a `LOB` or a `SQLT_LBI` that is longer than 4k.

Also, `LOB`s are not allowed in the following places:

- `LOB`s are not allowed in partitioned tables nor are they allowed in clustered tables and thus cannot be a cluster key.
- `LOB`s are not allowed in `GROUP` `BY`, `ORDER` `BY`, `SELECT` `DISTINCT`, aggregates and `JOINS`. However, `UNION` `ALL` is allowed on tables with `LOB`s. `UNION`, `MINUS`, and `SELECT` `DISTINCT` are allowed on `LOB` attributes if the object type has a `MAP` or `ORDER` function.
- `LOBS` are not analyzed in `ANALYZE`... `COMPUTE`/`ESTIMATE` `STATISTICS` statements.
- `LOB`s are not allowed in index only tables.
- `LOB`s are not allowed in `VARRAYs`.
- `NCLOB`s are not allowed as attributes in object types but `NCLOB` parameters are allowed in methods.
- Triggers are not supported on `LOB`s. However, you can use a `LOB` in the body of a trigger as follows:
  - you cannot write to a `LOB` (:old or :new value) in any kind of trigger.
  - in regular triggers you can read the :old value but you cannot read the :newvalue.
  - in `INSTEAD OF` triggers, you can read the :old and the :new values, which is to say that the :old and :new values can be read but not written.
  - you cannot specify `LOB` type columns in an `OF` clause, because `BFILE` types can be updated without updating the underlying table on which the trigger is defined.
  - using OCI functions or the `DBMS_LOB` package to update `LOB` values or `LOB` attributes of object columns will not fire triggers defined on the table containing the columns or the attributes.
- Client-side PL/SQL procedures may not call the `DBMS_LOB` package routines. However, you can use server-side PL/SQL procedures or anonymous blocks in PRO*C`` to call the `DBMS_LOB` package routines.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index