Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)

Part Number A89852-02
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page


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.

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.

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:

Oracle9i Application Developer's Guide - Large Objects (LOBs).  

This chapter discusses the following topics:

LOB Locators for DBMS_LOB

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. See also Chapter 1 of Oracle9i Application Developer's Guide - Large Objects (LOBs).

Internal LOBs

For internal LOBs, you must first use SQL data definition language (DDL) to define tables that contain LOB columns and then 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, Constants, and Exceptions for DBMS_LOB


Parameters for the DBMS_LOB subprograms use these datatypes:

Table 22-1 DBMS_LOB datatypes

A source or destination binary LOB


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


A source or destination character LOB (including NCLOB). 


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


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


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.


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.


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


Table 22-2 DBMS_LOB Exceptions
Exception  Code  Description 


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



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



The directory leading to the file does not exist. 



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



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. 



The operation attempted on the file failed. 



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



The number of open files has reached the maximum limit. 

Security for DBMS_LOB

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


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 Oracle9i Application Developer's Guide - Large Objects (LOBs) and the Oracle9i SQL Reference.

Rules and Limitations for DBMS_LOB

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

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

    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.

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

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

    The duration parameter passed to dbms_lob.createtemporary() is a hint. The duration of the new temp LOB is the same as the duration of the locator variable in PL/SQL. For example, in the program block above, the program variable a has the duration of the residing frame. Therefore at the end of the block, memory of a will be freed at the end of the function.

    If a PL/SQL package variable is used to create a temp LOB, it will have the duration of the package variable, which has a duration of SESSION.

       y clob; 
       dbms_lob.createtemporary(package.y, TRUE); 

    See Also:

    PL/SQL User's Guide and Reference. for more information on NOCOPY syntax 


Table 22-3 DBMS_LOB Exceptions
Exception  Code  Description 


Value for argument %s is not valid. 



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



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



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

Summary of DBMS_LOB Subprograms

Table 22-4 DBMS_LOB Subprograms  
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. 


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. 


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. 


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


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. 


Checks if the locator is pointing to a temporary LOB


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

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index