Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
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 chapter contains the following topics:
Overview
Security Model
Constants
Datatypes
Rules and Limits
Operational Notes
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.
This package must be created under SYS
. Operations provided by this package are performed under the current calling user, not under the package owner SYS
.
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.
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 onAUTHID
and privileges, see Oracle Database PL/SQL User's Guide and ReferenceYou can provide secure access to BFILEs
using the DIRECTORY
feature discussed in BFILENAME
function in the Oracle Database Application Developer's Guide - Large Objects and the Oracle Database SQL Reference.
For information about the security model pertaining to temporary LOB
s, see Operational Notes.
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 := 18446744073709551615; call CONSTANT PLS_INTEGER := 12; session CONSTANT PLS_INTEGER := 10;
The DBMS_LOB
package uses the datatypes shown in Table 52-1.
Table 52-1 Datatypes Used by DBMS_LOB
Type | Description |
---|---|
BLOB |
Source or destination binary LOB . |
RAW |
Source or destination RAW buffer (used with BLOB ). |
CLOB |
Source or destination character LOB (including NCLOB ). |
VARCHAR2 |
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 |
Large, binary object stored outside the database. |
The DBMS_LOB
package defines no special types.
An NCLOB
is a CLOB
for holding fixed-width and varying-width, multibyte national character sets.
The clause ANY_CS
in the specification of DBMS_LOB
subprograms for CLOB
s enables the CLOB
type to accept a CLOB
or NCLOB
locator variable as input.
The following rules apply in the specification of subprograms in this package:
length
, offset
, and amount
parameters for subprograms operating on BLOBs
and BFILEs
must be specified in terms of bytes.
length
, offset
, and amount
parameters for subprograms operating on CLOBs
must be specified in terms of characters.
A subprogram raises an INVALID_ARGVAL
exception if the following restrictions are not followed in specifying values for parameters (unless otherwise specified):
Only positive, absolute offsets from the beginning of LOB
data are permitted: Negative offsets from the tail of the LOB
are not permitted.
Only positive, nonzero values are permitted for the parameters that represent size and positional quantities, such as amount
, offset
, newlen
, nth
, and so on. Negative offsets and ranges observed in SQL string functions and operators are not permitted.
The value of offset
, amount
, newlen
, nth
must not exceed the value lobmaxsize
(4GB-1) in any DBMS_LOB
subprogram.
For CLOB
s consisting of fixed-width multibyte characters, the maximum value for these parameters must not exceed (lobmaxsize
/character_width_in_bytes
) characters.
For example, if the CLOB
consists of 2-byte characters, such as:
JA16SJISFIXED
Then, the maximum amount
value should not exceed:
4294967295/2 = 2147483647 characters.
PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for RAW
and VARCHAR2
parameters used in DBMS_LOB
subprograms. For example, if you declare a variable to be:
charbuf VARCHAR2(3000)
Then, charbuf
can hold 3000 single byte characters or 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB
subprograms for CLOBs
and NCLOBs
.
The %CHARSET
clause indicates that the form of the parameter with %CHARSET
must match the form of the ANY_CS
parameter 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 CHAR
data.
For DBMS_LOB
subprograms that take two CLOB
parameters, both CLOB
parameters must have the same form; that is, they must both be NCLOBs
, or they must both be CLOBs
.
If the value of amount
plus the offset
exceeds the maximum LOB
size allowed by the database, then access exceptions are raised.
Under these input conditions, read subprograms, such as READ
, COMPARE
, INSTR
, and SUBSTR
, read until End
of
Lob/File
is reached. For example, for a READ
operation on a BLOB
or BFILE
, if the user specifies offset
value of 3 GB and an amount
value of 2 GB, then READ
reads only ((4GB-1)-3GB
) bytes.
Functions with NULL
or invalid input values for parameters return a NULL
. Procedures with NULL
values for destination LOB
parameters raise exceptions.
Operations involving patterns as parameters, such as COMPARE
, INSTR
, and SUBSTR
do not support regular expressions or special matching characters (such as %
in the LIKE
operator in SQL) in the pattern
parameter or substrings.
The End
Of
LOB
condition is indicated by the READ
procedure using a NO_DATA_FOUND
exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB
. The READ
buffer for the last read contains 0 bytes.
For consistent LOB
updates, you must lock the row containing the destination LOB
before making a call to any of the procedures (mutators) that modify LOB
data.
Unless otherwise stated, the default value for an offset
parameter is 1, which indicates the first byte in the BLOB
or BFILE
data, and the first character in the CLOB
or NCLOB
value. No default values are specified for the amount
parameter — you must input the values explicitly.
You must lock the row containing the destination internal LOB
before calling any subprograms that modify the LOB
, such as APPEND
, COPY
, ERASE
, TRIM
, or WRITE
. These subprograms do not implicitly lock the row containing the LOB
.
The subprograms COMPARE
, INSTR
, READ
, SUBSTR
, FILECLOSE
, FILECLOSEALL
and LOADFROMFILE
operate only on an opened BFILE
locator; that is, a successful FILEOPEN
call must precede a call to any of these subprograms.
For the functions FILEEXISTS
, FILEGETNAME
and GETLENGTH
, a file's open/close status is unimportant; however, the file must exist physically, and you must have adequate privileges on the DIRECTORY
object and the file.
DBMS_LOB
does not support any concurrency control mechanism for BFILE
operations.
In the event of several open files in the session whose closure has not been handled properly, you can use the FILECLOSEALL
subprogram to close all files opened in the session and resume file operations from the beginning.
If you are the creator of a DIRECTORY
, or if you have system privileges, then use the CREATE
OR
REPLACE
, DROP
, and REVOKE
statements 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 preceding 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.
All files opened during a user session are implicitly closed at the end of the session. However, Oracle strongly recommends that you close the files after both normal and abnormal termination of operations on the BFILE.
In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES
.
In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because 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 SESSION_MAX_OPEN_FILES
value.
For example, consider a READ
operation past the end of the BFILE
value, which generates a NO_DATA_FOUND
exception:
DECLARE fil BFILE; pos INTEGER; amt BINARY_INTEGER; buf RAW(40); BEGIN SELECT ad_graphic INTO fil FROM print_media WHERE product_id = 3106; 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 ad_graphic INTO fil FROM print_media WHERE product_id = 3106;
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.
The maximum size of a LOB
supported by the database is equal to the value of the db_block_size
initialization parameter times the value 4294967295. This allows for a maximum LOB
size ranging from 8 terabytes to 128 terabytes.
The maximum buffer size, 32767 bytes, is represented by maxbufsize
.
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 file system. See also Chapter 1 of Oracle Database Application Developer's Guide - Large Objects.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain LOB
columns.
To populate your table with internal LOBs after LOB
columns are defined in a table, you use the SQL data manipulation language (DML) to initialize or populate the locators in the LOB
columns.
For an external LOB
(BFILE) to be represented by a LOB
locator, you must:
Ensure that a DIRECTORY
object representing a valid, existing physical directory has been defined, and that physical files (the LOBs you plan to add) exist with read permission for the database. If your operating system uses case-sensitive path names, then be sure you specify the directory in the correct format.
Pass the DIRECTORY
object and the filename of the external LOB
you are adding to the BFILENAME
function to create a LOB
locator for your external LOB
.
Once you have completed these tasks, you can insert or update a row containing a LOB
column using the given LOB
locator.
After the LOBs are defined and created, you can then SELECT
from 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.
For details on the different ways to do this, you must refer to the section of the Oracle Database Application Developer's Guide - Large Objects that describes "Accessing External LOBs (BFILEs)."
The database 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.
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
.
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.
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 for each temporary LOB
. The temporary LOB
locator can be passed by reference 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.
The database keeps track of temporary LOBs for each session in a v$
view called V$TEMPORARY_LOBS
, which contains information about how many temporary LOBs exist for each session. V$
views are for DBA use. From the session, the database 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.
The following notes are specific to temporary LOBs:
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
.
Operations based on CLOBs
do not verify if the character set IDs of the parameters (CLOB
parameters, VARCHAR2
buffers and patterns, and so on) match. It is the user's responsibility to ensure this.
Data storage resources are controlled by the DBA by creating different temporary tablespaces. DBAs can define separate temporary tablespaces for different users, if necessary.
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 round-trip 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
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); -- 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 preceding program block, 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
.
BEGIN y clob; END; / BEGIN dbms_lob.createtemporary(package.y, TRUE); END;
Table 52-2 DBMS_LOB Exceptions
Exception | Code | Description |
---|---|---|
INVALID_ARGVAL |
21560 |
The argument is expecting a nonNULL , 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 or the file for the operation. |
INVALID_DIRECTORY |
22287 |
The directory 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. |
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. |
Table 52-3 DBMS_LOB Package Subprograms
Subprogram | Description |
---|---|
APPEND Procedures |
Appends the contents of the source LOB to the destination LOB |
CLOSE Procedure |
Closes a previously opened internal or external LOB |
COMPARE Functions |
Compares two entire LOBs or parts of two LOBs |
CONVERTTOBLOB Procedure |
Reads character data from a source CLOB or NCLOB instance, converts the character data to the specified character, writes the converted data to a destination BLOB instance in binary format, and returns the new offsets |
CONVERTTOCLOB Procedure |
Takes a source BLOB instance, converts the binary data in the source instance to character data using the specified character, writes the character data to a destination CLOB or NCLOB instance, and returns the new offsets |
COPY Procedures |
Copies all, or part, of the source LOB to the destination LOB |
CREATETEMPORARY Procedures |
Creates a temporary BLOB or CLOB and its corresponding index in the user's default temporary tablespace |
ERASE Procedures |
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 object name and file name |
FILEISOPEN Function |
Checks if the file was opened using the input BFILE locators |
FILEOPEN Procedure |
Opens a file |
FREETEMPORARY Procedures |
Frees the temporary BLOB or CLOB in the user's default temporary tablespace |
GETCHUNKSIZE Functions |
Returns the amount of space used in the LOB chunk to store the LOB value |
GETLENGTH Functions |
Gets the length of the LOB value |
GET_STORAGE_LIMIT |
Returns the storage limit for LOBs in your database configuration |
INSTR Functions |
Returns the matching position of the nth occurrence of the pattern in the LOB |
ISOPEN Functions |
Checks to see if the LOB was already opened using the input locator |
ISTEMPORARY Functions |
Checks if the locator is pointing to a temporary LOB |
LOADBLOBFROMFILE Procedure |
Loads BFILE data into an internal BLOB |
LOADCLOBFROMFILE Procedure |
Loads BFILE data into an internal CLOB |
LOADFROMFILE Procedure |
Loads BFILE data into an internal LOB |
OPEN Procedures |
Opens a LOB (internal, external, or temporary) in the indicated mode |
READ Procedures |
Reads data from the LOB starting at the specified offset |
SUBSTR Functions |
Returns part of the LOB value starting at the specified offset |
TRIM Procedures |
Trims the LOB value to the specified shorter length |
WRITE Procedures |
Writes data to the LOB from a specified offset |
WRITEAPPEND Procedures |
Writes a buffer to the end of a LOB |
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);
Parameters
Table 52-4 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 52-5 APPEND Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Either the source or the destination LOB is NULL . |
Usage Notes
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis 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);
Parameters
Table 52-6 CLOSE Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
Exceptions
No error is returned if the BFILE
exists but is not opened. An error is returned if the LOB
is not open.
Usage Notes
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.
It is not mandatory that you wrap all LOB
operations inside the Open/Close APIs. However, if you open a LOB
, you must close it before you commit or rollback the transaction; an error is produced if you do not. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB
and non-LOB
data in the transaction are committed, but the domain and function-based indexes are not updated. If this happens, you should rebuild the functional and domain indexes on the LOB
column.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis function compares two entire LOBs or parts of two LOBs.
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 52-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. |
Return Values
INTEGER
: Zero if the comparison succeeds, nonzero if not.
NULL
, if
amount
< 1
amount
> LOBMAXSIZE
offset_1
or offset_2
< 1
offset_1
or offset_2
> LOBMAXSIZE
Usage Notes
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 nonzero 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.
Exceptions
Table 52-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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis procedure reads character data from a source CLOB
or NCLOB
instance, converts the character data to the character set you specify, writes the converted data to a destination BLOB
instance in binary format, and returns the new offsets. You can use this interface with any combination of persistent or temporary LOB
instances as the source or destination.
Syntax
DBMS_LOB.CONVERTTOBLOB( dest_lob IN OUT NOCOPY BLOB, src_clob IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Parameters
Table 52-9 CONVERTTOBLOB Procedure Parameters
Parameter | Description |
---|---|
dest_lob |
LOB locator of the destination LOB instance. |
src_blob |
LOB locator of the source LOB instance. |
amount |
Number ofcharacters to convert from the source LOB .
If you want to copy the entire |
dest_offset |
(IN) Offset in bytes in the destination LOB for the start of the write. Specify a value of 1 to start at the beginning of the LOB .
|
src_offset |
(IN) Offset in characters in the source LOB for the start of the read.
|
blob_csid |
Desired character set ID of the converted data. |
lang_context |
(IN) Language context, such as shift status, for the current conversion.
This information is returned so you can use it for subsequent conversions without losing or misinterpreting any source data. For the very first conversion, or if do not care, use the default value of zero. |
warning |
(OUT) Warning message. This parameter indicates when something abnormal happened during the conversion. You are responsible for checking the warning message.
Currently, the only possible warning is — inconvertible character. This occurs when the character in the source cannot be properly converted to a character in destination. The default replacement character (for example, '?') is used in place of the inconvertible character. The return value of this error message is defined as the constant |
Usage Notes
Preconditions
Before calling the CONVERTTOBLOB
procedure, the following preconditions must be met:
Both the source and destination LOB
instances must exist.
If the destination LOB
is a persistent LOB
, the row must be locked. To lock the row, select the LOB
using the FOR
UPDATE
clause of the SELECT
statement.
Constants and Defaults
All parameters are required. You must pass a variable for each OUT
or IN OUT
parameter. You must pass either a variable or a value for each IN
parameter.
Table 52-10 gives a summary of typical values for each parameter. The first column lists the parameter, the second column lists the typical value, and the last column describes the result of passing the value. Note that constants are used for some values. These constants are defined in the dbmslob.sql
package specification file.
Table 52-10 DBMS_LOB.CONVERTTOBLOB Typical Values
Parameter | Value | Description |
---|---|---|
amount |
LOBMAXSIZE (IN) |
convert the entire file |
dest_offset |
1 (IN) |
start from the beginning |
src_offset |
1 (IN) |
start from the beginning |
csid |
DEFAULT_CSID (IN) |
default CSID , use same CSID as source LOB |
lang_context |
DEFAULT_LANG_CTX (IN) |
default language context |
warning |
NO_WARNING (OUT)
|
no warning message, success
character in source cannot be properly converted |
General Notes
You must specify the desired character set for the destination LOB
in the blob_csid
parameter. You can pass a zero value for blob_csid
. When you do so, the database assumes that the desired character set is the same as the source LOB
character set, and performs a binary copy of the data—no character set conversion is performed.
You must specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source LOB
. The amount
and src_offset
values are in characters and the dest_offset
is in bytes. To convert the entire LOB
, you can specify LOBMAXSIZE
for the amount
parameter.
Exceptions
Table 52-11 gives possible exceptions this procedure can throw. The first column lists the exception string and the second column describes the error conditions that can cause the exception.
Table 52-11 CONVERTTOBLOB Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
One or more of the following:
- - - - |
See Also:
Oracle Database Application Developer's Guide - Large Objects for more information on using LOBs in application developmentThis procedure takes a source BLOB
instance, converts the binary data in the source instance to character data using the character set you specify, writes the character data to a destination CLOB
or NCLOB
instance, and returns the new offsets. You can use this interface with any combination of persistent or temporary LOB
instances as the source or destination.
Syntax
DBMS_LOB.CONVERTTOCLOB( dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS, src_blob IN BLOB, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, blob_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Parameters
Table 52-12 CONVERTTOCLOB Procedure Parameters
Parameter | Description |
---|---|
dest_lob |
LOB locator of the destination LOB instance. |
src_blob |
LOB locator of the source LOB instance. |
amount |
Number of bytes to convert from the source LOB .
If you want to copy the entire BLOB, pass the constant |
dest_offset |
(IN) Offset in characters in the destination LOB for the start of the write. Specify a value of 1 to start at the beginning of the LOB .
|
src_offset |
(IN) Offset in bytes in the source LOB for the start of the read.
|
blob_csid |
Desired character set ID of the converted data. |
lang_context |
( IN ) Language context, such as shift status, for the current conversion.
This information is returned so you can use it for subsequent conversions without losing or misinterpreting any source data. For the very first conversion, or if do not care, use the default value of zero. |
warning |
Warning message. This parameter indicates when something abnormal happened during the conversion. You are responsible for checking the warning message.
Currently, the only possible warning is — inconvertible character. This occurs when the character in the source cannot be properly converted to a character in destination. The default replacement character (for example, '?') is used in place of the inconvertible character. The return value of this error message is defined as the constant |
Usage Notes
Preconditions
Before calling the CONVERTTOCLOB
procedure, the following preconditions must be met:
Both the source and destination LOB
instances must exist.
If the destination LOB
is a persistent LOB
, the row must be locked before calling the CONVERTTOCLOB
procedure. To lock the row, select the LOB
using the FOR UPDATE
clause of the SELECT
statement.
Constants and Defaults
All parameters are required. You must pass a variable for each OUT
or IN OUT
parameter. You must pass either a variable or a value for each IN
parameter.
Table 52-13 gives a summary of typical values for each parameter. The first column lists the parameter, the second column lists the typical value, and the last column describes the result of passing the value. Note that constants are used for some values. These constants are defined in the dbmslob.sql
package specification file.
Table 52-13 DBMS_LOB.CONVERTTOCLOB Typical Values
Parameter | Value | Description |
---|---|---|
amount |
LOBMAXSIZE (IN) |
convert the entire file |
dest_offset |
1 (IN) |
start from the beginning |
src_offset |
1 (IN) |
start from the beginning |
csid |
DEFAULT_CSID (IN) |
default CSID , use destination CSID |
lang_context |
DEFAULT_LANG_CTX (IN) |
default language context |
warning |
NO_WARNING (OUT)
|
no warning message, success
character in source cannot be properly converted |
General Notes
You must specify the desired character set for the destination LOB
in the blob_csid
parameter. You can pass a zero value for blob_csid
. When you do so, the database assumes that the BLOB
contains character data in the same character set as the destination CLOB, and performs a binary copy of the data to the destination LOB
, no character set conversion being performed.
You must specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BLOB
. The amount and src_offset
values are in bytes and the dest_offset
is in characters. To convert the entire BLOB, you can specify LOBMAXSIZE
for the amount
parameter.
Exceptions
Table 52-14 CONVERTTOCLOB Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
One or more of the following:
- - - - |
See Also:
Oracle Database Application Developer's Guide - Large Objects for more information on using LOBs in application developmentThis 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.
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);
Parameters
Table 52-15 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. |
Exceptions
Table 52-16 COPY Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or invalid. |
INVALID_ARGVAL |
Either:
- - - - |
Usage Notes
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
.
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis 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);
Parameters
Table 52-17 CREATETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
cache |
Specifies if LOB should be read into buffer cache or not. |
dur |
1 of 2 predefined duration values (SESSION or CALL ) which specifies a hint as to whether the temporary LOB is cleaned up at the end of the session or call.
If |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
Oracle Database PL/SQL User's Guide and Reference for more information about NOCOPY and passing temporary lobs as parameters
This procedure erases an entire internal LOB
or part of an internal LOB.
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);
Parameters
Table 52-18 ERASE Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the LOB to be erased.For more information, see Operational Notes. |
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 ). |
Usage Notes
Note:
The length of theLOB
is not decreased when a section of the LOB is erased. To decrease the length of the LOB value, see the "TRIM Procedures".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.
Exceptions
Table 52-19 ERASE Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any input parameter is NULL . |
INVALID_ARGVAL |
Either:
- - |
Usage Notes
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure closes a BFILE
that has already been opened through the input locator.
Note:
The database has only read-only access toBFILEs
. This means that BFILEs
cannot be written through the database.Syntax
DBMS_LOB.FILECLOSE ( file_loc IN OUT NOCOPY BFILE);
Parameters
Table 52-20 FILECLOSE Procedure Parameters
Parameter | Description |
---|---|
file_loc |
Locator for the BFILE to be closed. |
Exceptions
Table 52-21 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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure closes all BFILEs
opened in the session.
Syntax
DBMS_LOB.FILECLOSEALL;
Exceptions
Table 52-22 FILECLOSEALL Procedure Exception
Exception | Description |
---|---|
UNOPENED_FILE |
No file has been opened in the session. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This function finds out if a given BFILE
locator points to a file that actually exists on the server's file system.
Syntax
DBMS_LOB.FILEEXISTS ( file_loc IN BFILE) RETURN INTEGER;
Pragmas
pragma restrict_references(FILEEXISTS, WNDS, RNDS, WNPS, RNPS);
Parameters
Return Values
Table 52-24 FILEEXISTS Function Return Values
Return | Description |
---|---|
0 |
Physical file does not exist. |
1 |
Physical file exists. |
Exceptions
Table 52-25 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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure determines the directory object and filename, given a BFILE
locator. This function only indicates the directory object 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 path name, it is 2000.
Syntax
DBMS_LOB.FILEGETNAME ( file_loc IN BFILE, dir_alias OUT VARCHAR2, filename OUT VARCHAR2);
Parameters
Table 52-26 FILEGETNAME Procedure Parameters
Parameter | Description |
---|---|
file_loc |
Locator for the BFILE |
dir_alias |
Directory object name |
filename |
Name of the BFILE |
Exceptions
Table 52-27 FILEGETNAME Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
dir_alias or filename are NULL . |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis function finds out whether a BFILE
was opened with the given FILE
locator.
Syntax
DBMS_LOB.FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER;
Pragmas
PRAGMA RESTRICT_REFERENCES(fileisopen, WNDS, RNDS, WNPS, RNPS);
Parameters
Return Values
INTEGER
: 0 = file is not open, 1 = file is open
Usage Notes
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.
Exceptions
Table 52-29 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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure opens a BFILE
for read-only access. BFILE
data may not be written through the database.
Syntax
DBMS_LOB.FILEOPEN ( file_loc IN OUT NOCOPY BFILE, open_mode IN BINARY_INTEGER := file_readonly);
Parameters
Table 52-30 FILEOPEN Procedure Parameters
Parameter | Description |
---|---|
file_loc |
Locator for the BFILE . |
open_mode |
File access is read-only. |
Exceptions
Table 52-31 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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure frees the temporary BLOB
or CLOB
in your default temporary tablespace.
Syntax
DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY BLOB); DBMS_LOB.FREETEMPORARY ( lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
Parameters
Table 52-32 FREETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator.For more information, see Operational Notes. |
Usage Notes
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.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThe DBMS_LOB.GET_STORAGE_LIMIT
function returns the LOB
storage limit for your database configuration. The DBMS_LOB
package supports LOB
instances up to this storage limit in size.
Syntax
DBMS_LOB.GET_STORAGE_LIMIT RETURN INTEGER;
Return Value
The value returned from this function is the maximum allowable size for LOB
instances for your database configuration. The return value depends on your DB_BLOCK_SIZE
initialization parameter setting and is calculated as (4 gigabytes minus 1) times the value of the DB_BLOCK_SIZE
initialization parameter.
Usage
Note that BLOB instances are sized in bytes while CLOB and NCLOB instances are sized in characters.
See Also:
Oracle Database Application Developer's Guide - Large Objects for details on LOB storage limitsWhen creating the table, you can specify the chunking factor, which can be a multiple of database 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 52-33 GETCHUNKSIZE Function Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
Return Values
The value returned for BLOBs
is in terms of bytes. The value returned for CLOBs
is in terms of characters.
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.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis 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 ( file_loc IN BFILE) RETURN INTEGER;
Pragmas
pragma restrict_references(GETLENGTH, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 52-34 GETLENGTH Function Parameter
Parameter | Description |
---|---|
file_loc |
The file locator for the LOB whose length is to be returned. |
Return Values
The length of the LOB
in bytes or characters as an INTEGER
. NULL
is returned if the input LOB
is NULL
or if the input lob_loc
is NULL
. An error is returned in the following cases for BFILEs
:
lob_loc
does not have the necessary directory and operating system privileges
lob_loc
cannot be read because of an operating system read error
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis function returns the matching position of the nth occurrence of the pattern in the LOB
, starting from the offset you specify.
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 ( file_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 52-35 INSTR Function Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the LOB to be examined. For more information, see Operational Notes. |
file_loc |
The file 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. |
Return Values
Table 52-36 INSTR Function Return Values
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 - - - |
Usage Notes
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.
Exceptions
Table 52-37 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. |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
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 52-38 ISOPEN Function Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
file_loc |
File locator. |
Usage Notes
For BFILES
, openness is associated with the locator. If the input locator was never passed to OPEN,
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.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis function determines whether a LOB
instance is temporary.
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 52-39 ISTEMPORARY Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
Return Values
This function returns TRUE
in temporary if the locator is pointing to a temporary LOB
. It returns FALSE
otherwise.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis procedure loads data from BFILE
to internal BLOB
. This achieves the same outcome as LOADFROMFILE
, and returns the new offsets.
Syntax
DBMS_LOB.LOADBLOBFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER);
Parameters
Table 52-40 LOADBLOBFROMFILE Procedure Parameters
Parameter | Descriptionw |
---|---|
dest_lob |
BLOB locator of the target for the load. |
src_bfile |
BFILE locator of the source for the load. |
amount |
Number of bytes to load from the BFILE . You can also use DBMS_LOB.LOBMAXSIZE to load until the end of the BFILE . |
dest_offset |
(IN ) Offset in bytes in the destination BLOB (origin: 1) for the start of the write. (OUT) New offset in bytes in the destination BLOB right after the end of this write, which is also where the next write should begin. |
src_offset |
(IN) Offset in bytes in the source BFILE (origin: 1) for the start of the read.(OUT) Offset in bytes in the source BFILE right after the end of this read, which is also where the next read should begin. |
Usage Notes
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 in bytes for BLOBs
.
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
. 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
(unless the amount specified is LOBMAXSIZE
which you can specify to continue loading until the end of the BFILE
is reached).
It is not mandatory that you wrap the LOB
operation inside the OPEN/CLOSE
operations. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the OPEN/CLOSE
, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB within the OPEN
or CLOSE
statement.
Constants and Defaults
There is no easy way to omit parameters. You must either declare a variable for IN/OUT
parameter or provide a default value for the IN
parameter. Here is a summary of the constants and the defaults that can be used.
Table 52-41 Suggested Values of the Parameter
Parameter | Default Value | Description |
---|---|---|
amount |
DBMSLOB.LOBMAXSIZE (IN) |
Load the entire file |
dest_offset |
1 (IN) |
start from the beginning |
src_offset |
1 (IN) |
start from the beginning |
Constants defined in DBMSLOB.SQL
lobmaxsize CONSTANT INTEGER := 4294967295;
Exceptions
Table 52-42 LOADBLOBFROMFILE Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
Either:
- - - - |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis procedure loads data from a BFILE
to an internal CLOB/NCLOB
with necessary character set conversion and returns the new offsets.
Syntax
DBMS_LOB.LOADCLOBFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_bfile IN BFILE, amount IN INTEGER, dest_offset IN OUT INTEGER, src_offset IN OUT INTEGER, bfile_csid IN NUMBER, lang_context IN OUT INTEGER, warning OUT INTEGER);
Parameters
Table 52-43 LOADCLOBFROMFILE Procedure Parameters
Parameter | Description |
---|---|
dest_lob |
CLOB/NCLOB locator of the target for the load. |
src_bfile |
BFILE locator of the source for the load. |
amount |
Number of bytes to load from the BFILE . Use DBMS_LOB .LOBMAXSIZE to load until the end of the BFILE . |
dest_offset |
(IN) Offset in characters in the destination CLOB (origin: 1) for the start of the write. (OUT) The new offset in characters right after the end of this load, which is also where the next load should start. It always points to the beginning of the first complete character after the end of load. If the last character is not complete, offset goes back to the beginning of the partial character. |
src_offset |
(IN) Offset in bytes in the source BFILE (origin: 1) for the start of the read.(OUT) Offset in bytes in the source BFILE right after the end of this read, which is also where the next read should begin. |
bfile_csid |
Character set id of the source (BFILE ) file. |
lang_context |
(IN) Language context, such as shift status, for the current load.
|
warning |
(OUT) Warning message. This indicates something abnormal happened during the loading. It may or may not be caused by the user's mistake. The loading is completed as required, and it's up to the user to check the warning message. Currently, the only possible warning is the inconvertible character. This happens when the character in the source cannot be properly converted to a character in destination, and the default replacement character (for example, '?') is used in place. The message is defined as warn_inconvertible_char in DBMSLOB. |
Usage Notes
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 in characters for CLOBs
.
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 CLOB
. 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
(unless the amount specified is LOBMAXSIZE
which you can specify to continue loading until the end of the BFILE
is reached).
Note the following requirements:
The destination character set is always the same as the database character set in the case of CLOB
and national character set in the case of NCLOB
.
csid=0
indicates the default behavior that uses database csid
for CLOB
and national csid
for NCLOB
in the place of source csid
. Conversion is still necessary if it is of varying width
It is not mandatory that you wrap the LOB
operation inside the OPEN/CLOSE
operations. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the OPEN/CLOSE
, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
Constants
Here is a summary of the constants and the suggested values that can be used.
Table 52-44 Suggested Values of the Parameter
Parameter | Suggested Value | Description |
---|---|---|
amount |
DBMSLOB.LOBMAXSIZE (IN) |
Load the entire file |
dest_offset |
1 (IN) |
start from the beginning |
src_offset |
1 (IN) |
start from the beginning |
csid |
0 (IN) |
default csid, use destination csid |
lang_context |
0 (IN) |
default language context |
warning |
0 (OUT) |
no warning message, everything is ok |
Constants defined in DBMSLOB.SQL
lobmaxsize CONSTANT INTEGER := 18446744073709551615; warn_inconvertible_char CONSTANT INTEGER := 1; default_csid CONSTANT INTEGER := 0; default_lang_ctx CONSTANT INTEGER := 0; no_warning CONSTANT INTEGER := 0;
Exceptions
Table 52-45 LOADCLOBFROMFILE Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
Either:
- - - - |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis procedure copies all, or a part of, a source external LOB
(BFILE
) to a destination internal LOB
.
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);
Parameters
Table 52-46 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. |
Usage Notes
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 inputBFILE
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
.
Note:
If the character set is varying width, UTF-8 for example, theLOB
value is stored in the fixed-width UCS2 format. Therefore, if you are using DBMS_LOB.LOADFROMFILE,
the data in the BFILE should be in the UCS2 character set instead of the UTF-8 character set. However, you should use sql*loader
instead of LOADFROMFILE
to load data into a CLOB or NCLOB because sql*loader
will provide the necessary character set conversions.It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
Exceptions
Table 52-47 LOADFROMFILE Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of the input parameters are NULL or INVALID . |
INVALID_ARGVAL |
Either:
- - - - |
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis procedure opens a LOB
, internal or external, in the indicated mode. Valid modes include read-only, and read/write.
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);
Parameters
Table 52-48 OPEN Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
LOB locator. For more information, see Operational Notes. |
open_mode |
Mode in which to open.
For For |
Usage Notes
Note:
If theLOB
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.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.
It is not mandatory that you wrap all LOB
operations inside the Open/Close APIs. However, if you open a LOB
, you must close it before you commit or rollback the transaction; an error is produced if you do not. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB
and nonLOB data in the transaction are committed, but the domain and function-based indexes are not updated. If this happens, you should rebuild the functional and domain indexes on the LOB
column.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis 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 ( file_loc IN BFILE, amount IN OUT NOCOPY BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW);
Parameters
Table 52-49 READ Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the LOB to be read. For more information, see Operational Notes. |
file_loc |
The file locator for the LOB to be examined. |
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. |
Exceptions
Table 52-50 lists exceptions that apply to any LOB
instance. Table 52-51 lists exceptions that apply only to BFILEs.
Table 52-50 READ Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
Any of lob_loc , amount , or offset parameters are NULL . |
INVALID_ARGVAL |
Either:
- - - - - |
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. |
Table 52-51 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. The database converts the LOB
value from the server's character set to the client's character set before it returns the buffer to the user.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedureThis 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 greater than (32767/n
), then SUBSTR
returns a character buffer of length (32767/n
), or the length of the CLOB
, whichever is lesser. For CLOBs in a varying-width character set, n
is the maximum byte-width used for characters in the CLOB.
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 ( file_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;
Pragmas
pragma restrict_references(SUBSTR, WNDS, WNPS, RNDS, RNPS);
Parameters
Table 52-52 SUBSTR Function Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the LOB to be read. For more information, see Operational Notes. |
file_loc |
The file locator for the LOB to be examined. |
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). |
Return Values
Table 52-53 SUBSTR Function Return Values
Return | Description |
---|---|
RAW |
Function overloading that has a BLOB or BFILE in parameter. |
VARCHAR2 |
CLOB version. |
NULL |
Either:
- any input parameter is - - - - |
Exceptions
Table 52-54 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. The database converts the LOB
value from the server's character set to the client's character set before it returns the buffer to the user.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this 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:
TheTRIM
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);
Parameters
Table 52-55 RIM Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the internal LOB whose length is to be trimmed. For more information, see Operational Notes. |
newlen |
New, trimmed length of the LOB value in bytes for BLOBs or characters for CLOBs . |
Exceptions
Table 52-56 TRIM Procedure Exceptions
Exception | Description |
---|---|
VALUE_ERROR |
lob_loc is NULL . |
INVALID_ARGVAL |
Either:
- - |
Usage Notes
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this 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.
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);
Parameters
Table 52-57 WRITE Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the internal LOB to be written to. For more information, see Operational Notes. |
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. |
Exceptions
Table 52-58 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:
- - - - |
Usage Notes
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.
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. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB
.
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure
This procedure writes a specified amount of data to the end of an internal LOB
. The data is written from the buffer
parameter.
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);
Parameters
Table 52-59 WRITEAPPEND Procedure Parameters
Parameter | Description |
---|---|
lob_loc |
Locator for the internal LOB to be written to. For more information, see Operational Notes. |
amount |
Number of bytes (for BLOBs ) or characters (for CLOBs ) to write, or number that were written. |
buffer |
Input buffer for the write. |
Usage Notes
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
.
Exceptions
Table 52-60 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:
- - |
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. The database converts the client-side buffer to the server's character set before it writes the buffer data to the LOB
.
It is not mandatory that you wrap the LOB
operation inside the Open/Close APIs. If you did not open the LOB
before performing the operation, the functional and domain indexes on the LOB
column are updated during the call. However, if you opened the LOB
before performing the operation, you must close it before you commit or rollback the transaction. When an internal LOB
is closed, it updates the functional and domain indexes on the LOB
column.
If you do not wrap the LOB
operation inside the Open/Close API, the functional and domain indexes are updated each time you write to the LOB
. This can adversely affect performance. Therefore, it is recommended that you enclose write operations to the LOB
within the OPEN
or CLOSE
statement.
See Also:
Oracle Database Application Developer's Guide - Large Objects for additional details on usage of this procedure