Oracle8i Supplied Packages Reference
Release 8.1.5

A68001-01

Library

Product

Contents

Index

Prev Next

17
DBMS_LOB

The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs.

DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs. The bulk of the LOB operations are provided by this package.

See Also:

Oracle8i Application Developer's Guide - Large Objects (LOBs)  

Requirements

This package must be created under SYS (connect internal). Operations provided by this package are performed under the current calling user, not under the package owner SYS.

LOB Locators

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

Internal LOBs

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

External LOBs

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

After the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

Temporary LOBs

For temporary LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary LOBs can be either BLOBs, CLOBs, or NCLOBs.

Datatypes

Parameters for the DBMS_LOB subprograms use these datatypes:

Table 17-1 DBMS_LOB datatypes
BLOB
 

A source or destination binary LOB.  

RAW
 

A source or destination RAW buffer (used with BLOB).  

CLOB
 

A source or destination character LOB (including NCLOB).  

VARCHAR2
 

A source or destination character buffer (used with CLOB and NCLOB).  

INTEGER
 

Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.  

BFILE
 

A large, binary object stored outside the database.  

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

Constants

DBMS_LOB defines the following constants:

file_readonly CONSTANT BINARY_INTEGER := 0;
lob_readonly  CONSTANT BINARY_INTEGER := 0;
lob_readwrite CONSTANT BINARY_INTEGER := 1;
lobmaxsize    CONSTANT INTEGER        := 4294967295;
call          CONSTANT PLS_INTEGER    := 12;
session       CONSTANT PLS_INTEGER    := 10;

Oracle supports a maximum LOB size of 4 gigabytes (232). However, the amount and offset parameters of the package can have values between 1 and 4294967295 (232-1).

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


Note:

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


Exceptions

Table 17-2 DBMS_LOB Exceptions
Exception  Code  Description 
invalid_argval
 

21560  

The argument is expecting a non-NULL, valid value but the argument value passed in is NULL, invalid, or out of range.  

access_error
 

22925  

You are trying to write too much data to the LOB: LOB size is limited to 4 gigabytes.  

noexist_directory
 

22285  

The directory leading to the file does not exist.  

nopriv_directory
 

22286  

The user does not have the necessary access privileges on the directory alias and/or the file for the operation.  

invalid_directory
 

22287  

The directory alias used for the current operation is not valid if being accessed for the first time, or if it has been modified by the DBA since the last access.  

operation_failed
 

22288  

The operation attempted on the file failed.  

unopened_file
 

22289  

The file is not open for the required operation to be performed.  

open_toomany
 

22290  

The number of open files has reached the maximum limit.  

Security

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

With Oracle8i, when creating the procedure, users can set the AUTHID to indicate whether they want definer's rights or invoker's rights. For example:

CREATE PROCEDURE proc1 authid definer ...
 

or

CREATE PROCEDURE proc1 authid current_user ....

See Also:

For more information on AUTHID and privileges, see PL/SQL User's Guide and Reference.  

You can provide secure access to BFILEs using the DIRECTORY feature discussed in BFILENAME function in the Oracle8i Application Developer's Guide - Large Objects (LOBs) and the Oracle8i SQL Reference.

Rules and Limitations

BFILE-Specific Rules and Limitations

Temporary LOBs

Oracle8i supports the definition, creation, deletion, access, and update of temporary LOBs. Your temporary tablespace stores the temporary LOB data. Temporary LOBs are not permanently stored in the database. Their purpose is mainly to perform transformations on LOB data.

A temporary LOB is empty when it is created. By default, all temporary LOBs are deleted at the end of the session in which they were created. If a process dies unexpectedly or if the database crashes, then temporary LOBs are deleted, and the space for temporary LOBs is freed.

In Oracle8i, there is also an interface to let you group temporary LOBs together into a logical bucket. The duration represents this logical store for temporary LOBs. Each temporary LOB can have separate storage characteristics, such as CACHE/ NOCACHE. There is a default store for every session into which temporary LOBs are placed if you don't specify a specific duration. Additionally, you are able to perform a free operation on durations, which causes all contents in a duration to be freed.

There is no support for consistent read (CR), undo, backup, parallel processing, or transaction management for temporary LOBs. Because CR and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.

Because CR, undo, and versions are not generated for temporary LOBs, there is potentially a performance impact if you assign multiple locators to the same temporary LOB. Semantically, each locator should have its own copy of the temporary LOB.

A copy of a temporary LOB is created if the user modifies the temporary LOB while another locator is also pointing to it. The locator on which a modification was performed now points to a new copy of the temporary LOB. Other locators no longer see the same data as the locator through which the modification was made. A deep copy was not incurred by permanent LOBs in these types of situations, because CR snapshots and version pages enable users to see their own versions of the LOB cheaply.

You can gain pseudo-REF semantics by using pointers to locators in OCI and by having multiple pointers to locators point to the same temporary LOB locator, if necessary. In PL/SQL, you must avoid using more than one locator per temporary LOB. The temporary LOB locator can be passed "by ref" to other procedures.

Because temporary LOBs are not associated with any table schema, there are no meanings to the terms in-row and out-of-row temporary LOBs. Creation of a temporary LOB instance by a user causes the engine to create and return a 'locator' to the LOB data. The PL/SQL DBMS_LOB package, PRO*C, OCI, and other programmatic interfaces operate on temporary LOBs through these locators just as they do for permanent LOBs.

There is no support for client side temporary LOBs. All temporary LOBs reside in the server.

Temporary LOBs do not support the EMPTY_BLOB or EMPTY_CLOB functions that are supported for permanent LOBs. The EMPTY_BLOB function specifies the fact that the LOB is initialized, but not populated with any data.

A temporary LOB instance can only be destroyed by using OCI or the DBMS_LOB package by using the appropriate FREETEMPORARY or OCIDurationEnd statement.

A temporary LOB instance can be accessed and modified using appropriate OCI and DBMS_LOB statements, just as for regular permanent internal LOBs. To make a temporary LOB permanent, you must explicitly use the OCI or DBMS_LOB COPY command, and copy the temporary LOB into a permanent one.

Security is provided through the LOB locator. Only the user who created the temporary LOB is able to see it. Locators are not expected to be able to pass from one user's session to another. Even if someone did pass a locator from one session to another, they would not access the temporary LOBs from the original session. Temporary LOB lookup is localized to each user's own session. Someone using a locator from somewhere else is only able to access LOBs within his own session that have the same LOB ID. Users should not try to do this, but if they do, they are not able to affect anyone else's data.

Oracle keeps track of temporary LOBs per session in a v$ view called V$TEMPORARY_LOBS, which contains information about how many temporary LOBs exist per session. V$ views are for DBA use. From the session, Oracle can determine which user owns the temporary LOBs. By using V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS, a DBA can see how much space is being used by a session for temporary LOBs. These tables can be used by DBAs to monitor and guide any emergency cleanup of temporary space used by temporary LOBs.

Temporary LOBs Usage Notes

  1. All functions in DBMS_LOB return NULL if any of the input parameters are NULL. All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL.

  2. Operations based on CLOBs do not verify if the character set IDs of the parameters (CLOB parameters, VARCHAR2 buffers and patterns, etc.) match. It is the user's responsibility to ensure this.

  3. Data storage resources are controlled by the DBA by creating different temporary tablespaces. DBAs can define separate temporary tablespaces for different users, if necessary.

  4. Temporary LOBs still adhere to value semantics in order to be consistent with permanent LOBs and to try to conform to the ANSI standard for LOBs. As a result, each time a user does an OCILobLocatatorAssign, or the equivalent assignment in PL/SQL, the database makes a copy of the temporary LOB.

    Each locator points to its own LOB value. If one locator is used to create a temporary LOB, and if it is assigned to another LOB locator using OCILobLOcatorAssign in OCI or through an assignment operation in PL/SQL, then the database copies the original temporary LOB and causes the second locator to point to the copy.

    In order for users to modify the same LOB, they must go through the same locator. In OCI, this can be accomplished fairly easily by using pointers to locators and assigning the pointers to point to the same locator. In PL/SQL, the same LOB variable must be used to update the LOB to get this effect.

    The following example shows a place where a user incurs a copy, or at least an extra roundtrip to the server.

    DECLARE 
      a blob; 
      b blob; 
    BEGIN 
      dbms_lob.createtemporary(b, TRUE, dbms_lob.session); 
      -- the following assignment results in a deep copy 
      a := b; 
    END; 
     
    

    The PL/SQL compiler makes temporary copies of actual arguments bound to OUT or IN OUT parameters. If the actual parameter is a temporary LOB, then the temporary copy is a deep (value) copy.

    The following PL/SQL block illustrates the case where the user incurs a deep copy by passing a temporary LOB as an IN OUT parameter.

    DECLARE 
      a blob; 
      procedure foo(parm IN OUT blob) is 
      BEGIN 
       ... 
      END; 
    BEGIN 
      dbms_lob.createtemporary(a, TRUE, dbms_lob.session); 
      -- the following call results in a deep copy of the blob a 
      foo(a); 
    END; 
     
    

    To minimize deep copies on PL/SQL parameter passing, use the NOCOPY compiler hint where possible.

    See Also:

    For more information on NOCOPY syntax, see PL/SQL User's Guide and Reference.  

Temporary LOB Exceptions

Table 17-3 DBMS_LOB Package Exceptions
Exception  Code  Description 
INVALID_ARGVAL
 

21560  

Value for argument %s is not valid.  

ACCESS_ERROR
 

22925  

Attempt to read or write beyond maximum LOB size on %s.  

NO_DATA_FOUND
 

 

EndofLob indicator for looping read operations. This is not a hard error.  

VALUE_ERROR
 

6502  

PL/SQL error for invalid values to subprogram's parameters.  

Summary of Subprograms

Table 17-4 DBMS_LOB Subprograms (Page 1 of 2)
Subprogram  Description 
APPEND procedure
 

Appends the contents of the source LOB to the destination LOB.  

CLOSE procedure
 

Closes a previously opened internal or external LOB.  

COMPARE function 
 

Compares two entire LOBs or parts of two LOBs.  

COPY procedure
 

Copies all, or part, of the source LOB to the destination LOB.  

CREATETEMPORARY procedure
 

Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace.  

ERASE procedure
 

Erases all or part of a LOB.  

FILECLOSE procedure
 

Closes the file.  

FILECLOSEALL procedure
 

Closes all previously opened files.  

FILEEXISTS function
 

Checks if the file exists on the server.  

FILEGETNAME procedure 
 

Gets the directory alias and file name.  

FILEISOPEN function
 

Checks if the file was opened using the input BFILE locators.  

FILEOPEN procedure
 

Opens a file.  

FREETEMPORARY procedure
 

Frees the temporary BLOB or CLOB in the user's default temporary tablespace.  

GETCHUNKSIZE function
 

Returns the amount of space used in the LOB chunk to store the LOB value.  

GETLENGTH function
 

Gets the length of the LOB value.  

INSTR function
 

Returns the matching position of the nth occurrence of the pattern in the LOB.  

ISOPEN function
 

Checks to see if the LOB was already opened using the input locator.  

ISTEMPORARY function
 

Checks if the locator is pointing to a temporary LOB.  

LOADFROMFILE procedure
 

Loads BFILE data into an internal LOB.  

OPEN procedure
 

Opens a LOB (internal, external, or temporary) in the indicated mode.  

READ procedure
 

Reads data from the LOB starting at the specified offset.  

SUBSTR function
 

Returns part of the LOB value starting at the specified offset.  

TRIM procedure
 

Trims the LOB value to the specified shorter length.  

WRITE procedure
 

Writes data to the LOB from a specified offset.  

WRITEAPPEND procedure
 

Writes a buffer to the end of a LOB.  

APPEND procedure

This procedure appends the contents of a source internal LOB to a destination LOB. It appends the complete source LOB.

There are two overloaded APPEND procedures.

Syntax

DBMS_LOB.APPEND (
   dest_lob IN OUT  NOCOPY BLOB, 
   src_lob  IN             BLOB); 

DBMS_LOB.APPEND (
   dest_lob IN OUT  NOCOPY CLOB  CHARACTER SET ANY_CS, 
   src_lob  IN             CLOB  CHARACTER SET dest_lob%CHARSET);

Pragmas

None.

Parameters

Table 17-5 APPEND Procedure Parameters
Parameter  Description 
dest_lob
 

Locator for the internal LOB to which the data is to be appended.  

src_lob
 

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

Exceptions

Table 17-6 APPEND Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Either the source or the destination LOB is NULL.  

Examples

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

CLOSE procedure

This procedure closes a previously opened internal or external LOB.

Syntax

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY BLOB); 

DBMS_LOB.CLOSE (
   lob_loc    IN OUT NOCOPY CLOB CHARACTER SET ANY_CS); 

DBMS_LOB.CLOSE (
   file_loc   IN OUT NOCOPY BFILE); 

Pragmas

None.

Errors

No error is returned if the BFILE exists but is not opened. An error is returned if the LOB is not open.

Usage Requirements

CLOSE requires a round-trip to the server for both internal and external LOBs. For internal LOBs, CLOSE triggers other code that relies on the close call, and for external LOBs (BFILEs), CLOSE actually closes the server-side operating system file.

COMPARE function

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

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

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

Syntax

DBMS_LOB.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;

DBMS_LOB.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; 

DBMS_LOB.COMPARE (
   lob_1            IN BFILE,
   lob_2            IN BFILE,
   amount           IN INTEGER,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

Pragmas

pragma restrict_references(COMPARE, WNDS, WNPS, RNDS, RNPS);

Parameters

Table 17-7 COMPARE Function Parameters
Parameter  Description 
lob_1
 

LOB locator of first target for comparison.  

lob_2
 

LOB locator of second target for comparison.  

amount
 

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

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.  

Returns

Exceptions

Table 17-8 COMPARE Function Exceptions for BFILE operations
Exception  Description 
UNOPENED_FILE
 

File was not opened using the input locator.  

NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Examples

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

COPY procedure

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

If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, then 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, then 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 copies data from the src_offset to the end of the source LOB.

Syntax

DBMS_LOB.COPY (
  dest_lob    IN OUT NOCOPY BLOB,
  src_lob     IN            BLOB,
  amount      IN            INTEGER,
  dest_offset IN            INTEGER := 1,
  src_offset  IN            INTEGER := 1);

DBMS_LOB.COPY ( 
  dest_lob    IN OUT NOCOPY 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);

Pragmas

None.

Parameters

Table 17-9 COPY Procedure Parameters
Parameter  Description 
dest_lob
 

LOB locator of the copy target.  

src_lob
 

LOB locator of source for the copy.  

amount
 

Number of bytes (for BLOBs) or characters (for CLOBs) 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.  

Returns

None.

Exceptions

Table 17-10 COPY Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of the input parameters are NULL or invalid.  

INVALID_ARGVAL
 

Either:

- src_offset or dest_offset < 1

- src_offset or dest_offset > LOBMAXSIZE

- amount < 1

- amount > LOBMAXSIZE  

Examples

CREATE OR REPLACE PROCEDURE Example_3a IS
    lobd, lobs     BLOB; 
    dest_offset    INTEGER := 1
    src_offset     INTEGER := 1
    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, dest_offset, src_offset);
    COMMIT;
   EXCEPTION
        WHEN some_exception
        THEN handle_exception;
END;

CREATE OR REPLACE PROCEDURE Example_3b IS
    lobd, lobs     BLOB;
    dest_offset    INTEGER := 1
    src_offset     INTEGER := 1
    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, dest_offset, src_offset);
    COMMIT;
   EXCEPTION
        WHEN some_exception
        THEN handle_exception;
END;

CREATETEMPORARY procedure

This procedure creates a temporary BLOB or CLOB and its corresponding index in your default temporary tablespace.

Syntax

DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY BLOB,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := 10);
  
DBMS_LOB.CREATETEMPORARY (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   cache   IN            BOOLEAN,
   dur     IN            PLS_INTEGER := 10);

Pragmas

None.

Parameters

Table 17-11 CREATETEMPORARY Procedure Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

cache
 

Specifies if LOB should be read into buffer cache or not.  

dur
 

1 of 2 predefined duration values (SESSION or CALL) which specifies whether the temporary LOB is cleaned up at the end of the session or call.

If dur is omitted, then the session duration is used.  

Returns

None.

Exceptions

None.

Example

DBMS_LOB.CREATETEMPORARY(Dest_Loc, TRUE, DBMS_LOB.SESSION)

ERASE procedure

This procedure erases an entire internal LOB or part of an internal LOB.


Note:

The length of the LOB is not decreased when a section of the LOB is erased.To decrease the length of the LOB value, see the "TRIM procedure".  


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

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

Syntax

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   BLOB,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   CLOB CHARACTER SET ANY_CS,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

Pragmas

None.

Parameters

Table 17-12 ERASE Procedure Parameters
Parameter  Description 
lob_loc
 

Locator for the LOB to be erased.  

amount
 

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

offset
 

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

Returns

None.

Exceptions

Table 17-13 ERASE Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any input parameter is NULL.  

INVALID_ARGVAL
 

Either:

- amount < 1 or amount > LOBMAXSIZE

- offset < 1 or offset > LOBMAXSIZE  

Example

CREATE OR REPLACE 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:

"TRIM procedure"  

FILECLOSE procedure

This procedure closes a BFILE that has already been opened via the input locator.


Note:

Oracle has only read-only access to BFILEs. This means that BFILEs cannot be written through Oracle.  


Syntax

DBMS_LOB.FILECLOSE (
    file_loc IN OUT NOCOPY BFILE); 

Pragmas

None.

Parameters

Table 17-14 FILECLOSE Procedure Parameter
Parameter  Description 
file_loc
 

Locator for the BFILE to be closed.  

Returns

None.

Exceptions

Table 17-15 FILECLOSE Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

NULL input value for file_loc.  

UNOPENED_FILE
 

File was not opened with the input locator.  

NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Example

CREATE OR REPLACE 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:

"FILEOPEN procedure", "FILECLOSEALL procedure"  

FILECLOSEALL procedure

This procedure closes all BFILEs opened in the session.

Syntax

DBMS_LOB.FILECLOSEALL; 

Pragmas

None.

Returns

None.

Exceptions

Table 17-16 FILECLOSEALL Procedure Exception
Exception  Description 
UNOPENED_FILE
 

No file has been opened in the session.  

Example

CREATE OR REPLACE 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:

"FILEOPEN procedure", "FILECLOSE procedure"  

FILEEXISTS function

This function finds out if a given BFILE locator points to a file that actually exists on the server's filesystem.

Syntax

DBMS_LOB.FILEEXISTS (
   file_loc     IN    BFILE)
  RETURN INTEGER; 

Pragmas

pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 17-17 FILEEXISTS Function Parameter
Parameter  Description 
file_loc
 

Locator for the BFILE.  

Returns

Table 17-18 FILEEXISTS Function Returns
Return  Description 
0
 

Physical file does not exist.  

1
 

Physical file exists.  

Exceptions

Table 17-19 FILEEXISTS Function Exceptions
Exception  Description 
NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

Example

CREATE OR REPLACE PROCEDURE Example_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:

"FILEISOPEN function"  

FILEGETNAME procedure

This procedure determines the directory 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.

The maximum constraint values for the dir_alias buffer is 30, and for the entire pathname is 2000.

Syntax

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

Pragmas

None.

Parameters

Table 17-20 FILEGETNAME Procedure Parameters
Parameter  Description 
file_loc
 

Locator for the BFILE.  

dir_alias
 

Directory alias.  

filename
 

Name of the BFILE.  

Returns

None.

Exceptions

Table 17-21 FILEGETNAME Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of the input parameters are NULL or INVALID.  

INVALID_ARGVAL
 

dir_alias or filename are NULL.  

Example

CREATE OR REPLACE 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;

FILEISOPEN function

This function finds out whether a BFILE was opened with the given FILE locator.

If the input FILE locator was never passed to the FILEOPEN procedure, then 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

DBMS_LOB.FILEISOPEN (
   file_loc   IN    BFILE)
  RETURN INTEGER; 

Pragmas

pragma restrict_references(FILEISOPEN, WNDS, RNDS, WNPS, RNPS);

Parameters

Table 17-22 FILEISOPEN Function Parameter
Parameter  Description 
file_loc
 

Locator for the BFILE.  

Returns

INTEGER: 0 = file is not open, 1 = file is open

Exceptions

Table 17-23 FILEISOPEN Function Exceptions
Exception  Description 
NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Example

CREATE OR REPLACE 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:

"FILEEXISTS function"  

FILEOPEN procedure

This procedure opens a BFILE for read-only access. BFILEs may not be written through Oracle.

Syntax

DBMS_LOB.FILEOPEN (
   file_loc   IN OUT NOCOPY  BFILE, 
   open_mode  IN             BINARY_INTEGER := file_readonly); 

Pragmas

None.

Parameters

Table 17-24 FILEOPEN Procedure Parameters
Parameter  Description 
file_loc
 

Locator for the BFILE.  

open_mode
 

File access is read-only.  

Returns

None.

Exceptions

Table 17-25 FILEOPEN Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

file_loc or open_mode is NULL.  

INVALID_ARGVAL
 

open_mode is not equal to FILE_READONLY.  

OPEN_TOOMANY
 

Number of open files in the session exceeds session_max_open_files.  

NOEXIST_DIRECTORY
 

Directory associated with file_loc does not exist.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Example

CREATE OR REPLACE 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:

"FILECLOSE procedure", "FILECLOSEALL procedure"  

FREETEMPORARY procedure

This procedure frees the temporary BLOB or CLOB in your default temporary tablespace. After the call to FREETEMPORARY, the LOB locator that was freed is marked as invalid.

If an invalid LOB locator is assigned to another LOB locator using OCILobLocatorAssign in OCI or through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.

Syntax

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY BLOB); 

DBMS_LOB.FREETEMPORARY (
   lob_loc  IN OUT  NOCOPY CLOB CHARACTER SET ANY_CS); 

Pragmas

None.

Parameters

Table 17-26 FREETEMPORARY Procedure Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

Returns

None.

Exceptions

None.

Example

DECLARE 
  a blob; 
  b blob; 
BEGIN 
  dbms_lob.createtemporary(a, TRUE, dbms_lob.session); 
  dbms_lob.createtemporary(b, TRUE, dbms_lob.session); 
  ... 
  -- the following call frees lob a 
  dbms_lob.freetemporary(a); 
  -- at this point lob locator a is marked as invalid 
  -- the following assignment frees the lob b and marks it as invalid 
also 
  b := a; 
END; 

GETCHUNKSIZE function

When creating the table, you can specify the chunking factor, which can be a multiple of Oracle blocks. This corresponds to the chunk size used by the LOB data layer when accessing or modifying the LOB value. Part of the chunk is used to store system-related information, and the rest stores the LOB value.

This function returns the amount of space used in the LOB chunk to store the LOB value.

Syntax

DBMS_LOB.GETCHUNKSIZE (
   lob_loc IN BLOB) 
  RETURN INTEGER; 

DBMS_LOB.GETCHUNKSIZE (
   lob_loc IN CLOB CHARACTER SET ANY_CS) 
  RETURN INTEGER; 

Pragmas

pragma restrict_references(GETCHUNKSIZE, WNDS, RNDS, WNPS, RNPS); 

Parameters

Table 17-27 GETCHUNKSIZE Function Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

Returns

The value returned for BLOBs is in terms of bytes. The value returned for CLOBs is in terms of characters.

Exceptions

None.

Usage Notes

Performance is improved if you enter read/write requests using a multiple of this chunk size. For writes, there is an added benefit, because LOB chunks are versioned, and if all writes are done on a chunk basis, then no extra or excess versioning is done or duplicated. You could batch up the WRITE until you have enough for a chunk, instead of issuing several WRITE calls for the same chunk.

GETLENGTH function

This function gets 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. 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

DBMS_LOB.GETLENGTH (
   lob_loc    IN  BLOB) 
  RETURN INTEGER;
 
DBMS_LOB.GETLENGTH (
   lob_loc    IN  CLOB   CHARACTER SET ANY_CS) 
  RETURN INTEGER; 

DBMS_LOB.GETLENGTH (
   lob_loc    IN  BFILE) 
  RETURN INTEGER;

Pragmas

pragma restrict_references(GETLENGTH, WNDS, WNPS, RNDS, RNPS);

Parameters

Table 17-28 GETLENGTH Function Parameter
Parameter  Description 
lob_loc
 

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

Returns

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

Exceptions

None.

Examples

CREATE OR REPLACE 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;

CREATE OR REPLACE 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;

INSTR function

This function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset you specify.

The form of the VARCHAR2 buffer (the pattern parameter) must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

For BFILEs, the file must 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

DBMS_LOB.INSTR (
   lob_loc    IN   BLOB,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.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;

DBMS_LOB.INSTR (
   lob_loc    IN   BFILE,
   pattern    IN   RAW,
   offset     IN   INTEGER := 1,
   nth        IN   INTEGER := 1)
  RETURN INTEGER;

Pragmas

pragma restrict_references(INSTR, WNDS, WNPS, RNDS, RNPS);

Parameters

Table 17-29 INSTR Function Parameters
Parameter  Description 
lob_loc
 

Locator for the LOB to be examined.  

pattern
 

Pattern to be tested for. The pattern is a group of RAW bytes for BLOBs, and a character string (VARCHAR2) for CLOBs.The maximum size of the pattern is 16383 bytes.  

offset
 

Absolute offset in bytes (BLOBs) or characters (CLOBs) at which the pattern matching is to start. (origin: 1)  

nth
 

Occurrence number, starting at 1.  

Returns

Table 17-30 INSTR Function Returns
Return  Description 
INTEGER
 

Offset of the start of the matched pattern, in bytes or characters.

It returns 0 if the pattern is not found.  

NULL
 

Either:

-any one or more of the IN parameters was NULL or INVALID.

-offset < 1 or offset > LOBMAXSIZE.

-nth < 1.

-nth > LOBMAXSIZE.  

Exceptions

Table 17-31 INSTR Function Exceptions for BFILES
Exception  Description 
UNOPENED_FILE
 

File was not opened using the input locator.  

NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Examples

CREATE OR REPLACE 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;

CREATE OR REPLACE PROCEDURE Example_12b IS
DECLARE
    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:

"SUBSTR function"  

ISOPEN function

This function checks to see if the LOB was already opened using the input locator. This subprogram is for internal and external LOBs.

Syntax

DBMS_LOB.ISOPEN (
   lob_loc IN BLOB) 
  RETURN INTEGER; 

DBMS_LOB.ISOPEN (
   lob_loc IN CLOB CHARACTER SET ANY_CS) 
  RETURN INTEGER; 

DBMS_LOB.ISOPEN (
   file_loc IN BFILE) 
  RETURN INTEGER; 

Pragmas

pragma restrict_references(ISOPEN, WNDS, RNDS, WNPS, RNPS); 

Parameters

Table 17-32 ISOPEN Function Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

file_loc
 

File locator.  

Exceptions

None.

Usage Notes

For BFILES, openness is associated with the locator. If the input locator was never passed to OPEN, then the BFILE is not considered to be opened by this locator. However, a different locator may have opened the BFILE. More than one OPEN can be performed on the same BFILE using different locators.

For internal LOBs, openness is associated with the LOB, not with the locator. If locator1 opened the LOB, then locator2 also sees the LOB as open. For internal LOBs, ISOPEN requires a round-trip, because it checks the state on the server to see if the LOB is indeed open.

For external LOBs (BFILEs), ISOPEN also requires a round-trip, because that's where the state is kept.

ISTEMPORARY function

Syntax

DBMS_LOB.ISTEMPORARY (
   lob_loc IN BLOB)
  RETURN INTEGER;
 
DBMS_LOB.ISTEMPORARY (
   lob_loc IN CLOB CHARACTER SET ANY_CS)
  RETURN INTEGER;

Pragmas

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

Parameters

Table 17-33 ISTEMPORARY Procedure Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

temporary
 

Boolean, which indicates whether the LOB is temporary or not.  

Returns

This function returns TRUE in temporary if the locator is pointing to a temporary LOB. It returns FALSE otherwise.

Exceptions

None.

LOADFROMFILE procedure

This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB.

You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. The amount and src_offset, because they refer to the BFILE, are in terms of bytes, and the dest_offset is either in bytes or characters for BLOBs and CLOBs respectively.


Note:

The input BFILE must have been opened prior to using this procedure. 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, then 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, then existing data is overwritten.

There is an error if the input amount plus offset exceeds the length of the data in the BFILE.

Syntax

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

Pragmas

None.

Parameters

Table 17-34 LOADFROMFILE Procedure Parameters
Parameter  Description 
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.  

Returns

None.

Exceptions

Table 17-35 LOADFROMFILE Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of the input parameters are NULL or INVALID.  

INVALID_ARGVAL
 

Either:

- src_offset or dest_offset < 1.

- src_offset or dest_offset > LOBMAXSIZE.

- amount < 1.

- amount > LOBMAXSIZE.  

Example

CREATE OR REPLACE PROCEDURE Example_l2f IS 
  lobd       BLOB; 
  fils       BFILE   := BFILENAME('SOME_DIR_OBJ','some_file'); 
  amt        INTEGER := 4000; 
BEGIN 
  SELECT b_lob INTO lobd FROM lob_table WHERE key_value = 42 FOR UPDATE;
  dbms_lob.fileopen(fils, dbms_lob.file_readonly); 
  dbms_lob.loadfromfile(lobd, fils, amt); 
  COMMIT; 
  dbms_lob.fileclose(fils); 
END;

OPEN procedure

This procedure opens a LOB, internal or external, in the indicated mode. Valid modes include read-only, and read-write. It is an error to open the same LOB twice.


Note:

If the LOB was opened in read-only mode, and if you try to write to the LOB, then an error is returned. BFILE can only be opened with read-only mode.  


In Oracle8.0, the constant file_readonly was the only valid mode in which to open a BFILE. For Oracle 8i, two new constants have been added to the DBMS_LOB package: lob_readonly and lob_readwrite.

Syntax

DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY BLOB,
   open_mode IN            BINARY_INTEGER);
 
DBMS_LOB.OPEN (
   lob_loc   IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
   open_mode IN            BINARY_INTEGER);
 
DBMS_LOB.OPEN (
   file_loc  IN OUT NOCOPY BFILE,
   open_mode IN            BINARY_INTEGER := file_readonly);

Pragmas

None.

Parameters

Table 17-36 OPEN Procedure Parameters
Parameter  Description 
lob_loc
 

LOB locator.  

open_mode
 

Mode in which to open.  

Usage Notes

OPEN requires a round-trip to the server for both internal and external LOBs. For internal LOBs, OPEN triggers other code that relies on the OPEN call. For external LOBs (BFILEs), OPEN requires a round-trip because the actual operating system file on the server side is being opened.

READ procedure

This procedure reads a piece of a LOB, and returns 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 input offset points past the End of LOB, then amount is set to 0, and a NO_DATA_FOUND exception is raised.

Syntax

DBMS_LOB.READ (
   lob_loc   IN             BLOB,
   amount    IN OUT  NOCOPY BINARY_INTEGER,
   offset    IN             INTEGER,
   buffer    OUT            RAW);

DBMS_LOB.READ (
   lob_loc   IN             CLOB CHARACTER SET ANY_CS,
   amount    IN OUT  NOCOPY BINARY_INTEGER,
   offset    IN             INTEGER,
   buffer    OUT            VARCHAR2 CHARACTER SET lob_loc%CHARSET); 

DBMS_LOB.READ (
   lob_loc   IN              BFILE,
   amount    IN OUT   NOCOPY BINARY_INTEGER,
   offset    IN              INTEGER,
   buffer    OUT             RAW);

Pragmas

None.

Parameters

Table 17-37 READ Procedure Parameters
Parameter  Description 
lob_loc
 

Locator for the LOB to be read.  

amount
 

Number of bytes (for BLOBs) or characters (for CLOBs) to read, or number that were read.  

offset
 

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

buffer
 

Output buffer for the read operation.  

Returns

None.

Exceptions

Table 17-38 READ Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of lob_loc, amount, or offset parameters are NULL.  

INVALID_ARGVAL
 

Either:

- amount < 1

- amount > MAXBUFSIZE

- offset < 1

- offset > LOBMAXSIZE

- amount is greater, in bytes or characters, than the capacity of buffer.  

NO_DATA_FOUND
 

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.  

Exceptions for BFILEs

Table 17-39 READ Procedure Exceptions for BFILEs
Exception  Description 
UNOPENED_FILE
 

File is not opened using the input locator.  

NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

When calling DBMS_LOB.READ from the client (for example, in a BEGIN/END block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.

Examples

CREATE OR REPLACE PROCEDURE Example_13a IS
    src_lob        BLOB;
    buffer         RAW(32767);
    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;

CREATE OR REPLACE 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:

CREATE OR REPLACE 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;

SUBSTR function

This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

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

Syntax

DBMS_LOB.SUBSTR (
   lob_loc     IN    BLOB,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

DBMS_LOB.SUBSTR (
   lob_loc     IN    BFILE,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN RAW;

Pragmas

pragma restrict_references(SUBSTR, WNDS, WNPS, RNDS, RNPS);

Parameters

Table 17-40 SUBSTR Function Parameters
Parameter  Description 
lob_loc
 

Locator for the LOB to be read.  

amount
 

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

offset
 

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

Returns

Table 17-41 SUBSTR Function Returns
Return  Description 
RAW
 

Function overloading that has a BLOB or BFILE in parameter.  

VARCHAR2
 

CLOB version.  

NULL
 

Either:

- any input parameter is NULL

- amount < 1

- amount > 32767

- offset < 1

- offset > LOBMAXSIZE  

Exceptions

Table 17-42 SUBSTR Function Exceptions for BFILE operations
Exception  Description 
UNOPENED_FILE
 

File is not opened using the input locator.  

NOEXIST_DIRECTORY
 

Directory does not exist.  

NOPRIV_DIRECTORY
 

You do not have privileges for the directory.  

INVALID_DIRECTORY
 

Directory has been invalidated after the file was opened.  

INVALID_OPERATION
 

File does not exist, or you do not have access privileges on the file.  

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

When calling DBMS_LOB.SUBSTR from the client (for example, in a BEGIN/END block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.

Examples

CREATE OR REPLACE 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;

CREATE OR REPLACE 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:

"INSTR function", "READ procedure"  

TRIM procedure

This procedure trims the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and specify the length in characters for CLOBs.


Note:

The TRIM procedure decreases the length of the LOB to the value specified in the newlen parameter.  


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

Syntax

DBMS_LOB.TRIM (
   lob_loc        IN OUT  NOCOPY BLOB,
   newlen         IN             INTEGER);

DBMS_LOB.TRIM (
   lob_loc        IN OUT  NOCOPY CLOB CHARACTER SET ANY_CS,
   newlen         IN             INTEGER);

Pragmas

None.

Parameters

Table 17-43 TRIM Procedure Parameters
Parameter  Description 
lob_loc
 

Locator for the internal LOB whose length is to be trimmed.  

newlen
 

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

Returns

None.

Exceptions

Table 17-44 TRIM Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

lob_loc is NULL.  

INVALID_ARGVAL
 

Either:

- new_len < 0

- new_len > LOBMAXSIZE  

Example
CREATE OR REPLACE 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:

"ERASE procedure", "WRITEAPPEND procedure"  

WRITE procedure

This procedure writes 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.

There 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, then only amount bytes or 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, then zero-byte fillers or spaces are inserted in the BLOB or CLOB respectively.

Syntax

DBMS_LOB.WRITE (
   lob_loc  IN OUT NOCOPY  BLOB,
   amount   IN             BINARY_INTEGER,
   offset   IN             INTEGER,
   buffer   IN             RAW);

DBMS_LOB.WRITE (
   lob_loc  IN OUT  NOCOPY CLOB   CHARACTER SET ANY_CS,
   amount   IN             BINARY_INTEGER,
   offset   IN             INTEGER,
   buffer   IN             VARCHAR2 CHARACTER SET lob_loc%CHARSET); 

Pragmas

None.

Parameters

Table 17-45 WRITE Procedure Parameters
Parameter  Description 
lob_loc
 

Locator for the internal LOB to be written to.  

amount
 

Number of bytes (for BLOBs) or characters (for CLOBs) to write, or number that were written.  

offset
 

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

buffer
 

Input buffer for the write.  

Returns

None.

Exceptions

Table 17-46 WRITE Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of lob_loc, amount, or offset parameters are NULL, out of range, or INVALID.  

INVALID_ARGVAL
 

Either:

- amount < 1

- amount > MAXBUFSIZE

- offset < 1

- offset > LOBMAXSIZE  

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

When calling DBMS_LOB.WRITE from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.

Example

CREATE OR REPLACE 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 UPDATE;
    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:

"APPEND procedure", "COPY procedure"  

WRITEAPPEND procedure

This procedure writes a specified amount of data to the end of an internal LOB. The data is written from the buffer parameter.

There 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, then only amount bytes or characters from the buffer are written to the end of the LOB.

Syntax

DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY BLOB, 
   amount  IN            BINARY_INTEGER, 
   buffer  IN            RAW); 

DBMS_LOB.WRITEAPPEND (
   lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, 
   amount  IN            BINARY_INTEGER, 
   buffer  IN            VARCHAR2 CHARACTER SET lob_loc%CHARSET); 

Pragmas

None.

Parameters

Table 17-47 WRITEAPPEND Procedure Parameters
Parameter  Description 
lob_loc
 

Locator for the internal LOB to be written to.  

amount
 

Number of bytes (for BLOBs) or characters (for CLOBs) to write, or number that were written.  

buffer
 

Input buffer for the write.  

Exceptions

Table 17-48 WRITEAPPEND Procedure Exceptions
Exception  Description 
VALUE_ERROR
 

Any of lob_loc, amount, or offset parameters are NULL, out of range, or INVALID.  

INVALID_ARGVAL
 

Either:

- amount < 1

- amount > MAXBUFSIZE  

Usage Notes

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

When calling DBMS_LOB.WRITEAPPEND from the client (for example, in a BEGIN/END block from within SQL*Plus), the buffer must contain data in the client's character set. Oracle converts the client-side buffer to the server's character set before it writes the buffer data to the LOB.

Example

CREATE OR REPLACE PROCEDURE Example_17 IS
    lob_loc    BLOB;
    buffer     RAW;
    amt        BINARY_INTEGER := 32767;
    i          INTEGER;
BEGIN
    SELECT b_col INTO lob_loc
        FROM lob_table
        WHERE key_value = 12 FOR UPDATE;
    FOR i IN 1..3 LOOP
        -- fill the buffer with data to be written to the lob
        dbms_lob.writeappend (lob_loc, amt, buffer);
    END LOOP;
END;

See Also:

"APPEND procedure", "COPY procedure", "WRITE procedure"  




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index