|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
Part Number A89852-02
DBMS_LOB package provides subprograms to operate on
BFILEs, and temporary
LOBs. You can use
DBMS_LOB to access and manipulation specific parts of a
LOB or complete
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
DBMS_LOB can read and modify
NCLOBs; it provides read-only operations for
BFILEs. The bulk of the
LOB operations are provided by this package.
This chapter discusses the following topics:
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).
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
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.
LOBs are defined and created, you may then
LOB locator into a local PL/SQL
LOB variable and use this variable as an input parameter to
DBMS_LOB for access to the
LOBs, you must use the OCI, PL/SQL, or another programmatic interface to create or manipulate them. Temporary
LOBs can be either
Parameters for the
DBMS_LOB subprograms use these datatypes:
A source or destination binary
A source or destination
A source or destination character
A source or destination character buffer (used with
Specifies the size of a buffer or
A large, binary object stored outside the database.
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
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
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
VARCHAR2 variable is 32767 bytes.
The argument is expecting a non-
You are trying to write too much data to the
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.
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:
For more information on
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.
offsetparameters for subprograms operating on
BFILEsmust be specified in terms of bytes.
offsetparameters for subprograms operating on
CLOBsmust be specified in terms of characters.
amountparameters are always in characters for
NCLOBsand in bytes for
INVALID_ARGVALexception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):
LOBdata are permitted: Negative offsets from the tail of the
LOBare not permitted.
nth, etc. Negative offsets and ranges observed in Oracle SQL string functions and operators are not permitted.
nthmust not exceed the value
lobmaxsize(4GB-1) in any
CLOBs consisting of fixed-width multi-byte characters, the maximum value for these parameters must not exceed (
For example, if the
CLOB consists of 2-byte characters, such as:
Then, the maximum
amount value should not exceed:
VARCHAR2parameters used in
DBMS_LOBsubprograms. For example, if you declare a variable to be:
charbuf can hold 3000 single byte characters or 1500 2-byte fixed width characters. This has an important consequence for
DBMS_LOB subprograms for
%CHARSETclause indicates that the form of the parameter with
%CHARSETmust match the form of the
ANY_CSparameter to which it refers.
For example, in
DBMS_LOB subprograms that take a
VARCHAR2 buffer parameter, the form of the
VARCHAR2 buffer must match the form of the
CLOB parameter. 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
DBMS_LOB subprograms that take two
CLOB parameters, both
CLOB parameters must have the same form; i.e., they must both be
NCLOBs, or they must both be
offsetexceeds 4 GB (i.e.,
BFILEs, and (l
CLOBsin calls to update subprograms (i.e.,
WRITEAPPENDsubprograms), then access exceptions are raised.
Under these input conditions, read subprograms, such as
SUBSTR, read until
Lob/File is reached. For example, for a
READ operation on a
BFILE, if the user specifies
offset value of 3 GB and an
amount value of 2 GB, then
READ reads only ((
NULLor invalid input values for parameters return a
NULL. Procedures with
NULLvalues for destination
LOBparameters raise exceptions.
SUBSTRdo not support regular expressions or special matching characters (such as
LIKEoperator in SQL) in the
patternparameter or substrings.
LOBcondition is indicated by the
READprocedure using a
NO_DATA_FOUNDexception. This exception is raised only upon an attempt by the user to read beyond the end of the
READbuffer for the last read contains 0 bytes.
LOBupdates, you must lock the row containing the destination
LOBbefore making a call to any of the procedures (mutators) that modify
offsetparameter is 1, which indicates the first byte in the
BFILEdata, and the first character in the
NCLOBvalue. No default values are specified for the
amountparameter -- you must input the values explicitly.
LOBbefore calling any subprograms that modify the
LOB, such as
WRITE. These subprograms do not implicitly lock the row containing the
LOADFROMFILEoperate only on an opened
BFILElocator; that is, a successful
FILEOPENcall must precede a call to any of these subprograms.
GETLENGTH, a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on the
DIRECTORYobject and the file.
DBMS_LOBdoes not support any concurrency control mechanism for
FILECLOSEALLsubprogram to close all files opened in the session and resume file operations from the beginning.
DIRECTORY, or if you have system privileges, then use the
REVOKEstatements in SQL with extreme caution.
If you, or other grantees of a particular directory object, have several open files in a session, then 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.
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
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 after an exception occurs, only the exception handler has access to the
BFILE variable in its most current state.
After the exception transfers program control outside the PL/SQL program block, all references to the open
BFILEs are lost. The result is a larger open file count which may or may not exceed the
For example, consider a
READ operation past the end of the
BFILE value, which generates a
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.open(fil, dbms_lob.lob_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.close(fil); END; ORA-01403: no data found ORA-06512: at "SYS.DBMS_LOB", line 373 ORA-06512: at line 10
After 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. Therefore, the solution is to use an exception handler:
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.open(fil, dbms_lob.lob_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.close(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, you should ensure that files opened in a PL/SQL block using
DBMS_LOB are closed before normal or abnormal termination of the block.
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 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
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
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
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.
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
There is no support for client side temporary
LOBs. All temporary
LOBs reside in the server.
LOBs do not support the
EMPTY_CLOB functions that are supported for permanent
EMPTY_BLOB function specifies the fact that the
LOB is initialized, but not populated with any data.
LOB instance can only be destroyed by using OCI or the
DBMS_LOB package by using the appropriate
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
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
NULLif any of the input parameters are
NULL. All procedures in
DBMS_LOBraise an exception if the
LOBlocator is input as
CLOBsdo not verify if the character set IDs of the parameters (
VARCHAR2buffers and patterns, etc.) match. It is the user's responsibility to ensure this.
LOBsstill adhere to value semantics in order to be consistent with permanent
LOBsand 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
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.
DECLARE a blob; b blob; BEGIN dbms_lob.createtemporary(b, TRUE); -- the following assignment results in a deep copy a := b; END;
The PL/SQL compiler makes temporary copies of actual arguments bound to
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
DECLARE a blob; procedure foo(parm IN OUT blob) is BEGIN ... END; BEGIN dbms_lob.createtemporary(a, TRUE); -- 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.
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
PL/SQL User's Guide and Reference. for more information on
Value for argument %s is not valid.
Attempt to read or write beyond maximum
PL/SQL error for invalid values to subprogram's parameters.
Appends the contents of the source
Closes a previously opened internal or external
Compares two entire
Copies all, or part, of the source LOB to the destination LOB.
Creates a temporary
Erases all or part of a
Closes the file.
Closes all previously opened files.
Checks if the file exists on the server.
Gets the directory alias and file name.
Checks if the file was opened using the input
Opens a file.
Frees the temporary
Returns the amount of space used in the
Gets the length of the
Returns the matching position of the nth occurrence of the pattern in the
Checks to see if the
Checks if the locator is pointing to a temporary
Reads data from the
Returns part of the
Writes data to the
Writes a buffer to the end of a