2 DBMS_LOB
TimesTen Classic supports LOBs (large objects). The DBMS_LOB
package provides subprograms to operate on BLOBs, CLOBs, and NCLOBs. You can use DBMS_LOB
to access and manipulate specific parts of LOBs or complete LOBs.
This chapter contains the following topics:
-
-
Overview
-
Security model
-
Constants
-
Data types
-
Rules and limits
-
Operational notes
-
Exceptions
-
You can also refer to Large objects (LOBs) in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
Using DBMS_LOB
Overview
DBMS_LOB
can read, manipulate, and modify BLOBs, CLOBs, and NCLOBs.
For an overview of LOBs, see Introduction to Large Objects and SecureFiles in Oracle Database SecureFiles and Large Objects Developer's Guide.
Security Model
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 Definer's Rights and Invoker's Rights (AUTHID Clause) in Oracle TimesTen In-Memory Database Security Guide. For information about the security model pertaining to temporary LOBs, see Operational Notes.
Constants
The DBMS_LOB
package uses the constants shown in Table 2-1:
Table 2-1 DBMS_LOB Constants
Constant | Type | Value | Description |
---|---|---|---|
|
|
12 |
Create the temporary LOB with call duration. |
|
|
0 |
This is the default character set ID. |
|
|
0 |
This is the default language context. |
|
|
0 |
Open the specified LOB read-only. |
|
|
1 |
Open the specified LOB read/write. |
|
|
16777216 (16 MB) |
Set maximum size of a BLOB in bytes. |
|
|
4194304 (4 MB) |
Set maximum size of a CLOB in bytes. |
|
|
0 |
Indicates success, no warning message. |
|
|
10 |
Create the temporary LOB with session duration. Note: In TimesTen, LOB duration cannot extend past the end of the transaction. Temporary LOB contents are destroyed when the corresponding locator is invalidated at the end of the transaction. |
|
|
11 |
Create the temporary LOB with transaction duration. |
|
|
1 |
Used by the conversion functions to indicate there is an inconvertible character. |
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_INTEGER
to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples. -
The
INTEGER
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
Data Types
The DBMS_LOB
package uses the data types shown in Table 2-2.
Table 2-2 Data Types Used by DBMS_LOB
Type | Description |
---|---|
|
Source or destination binary LOB |
|
Source or destination |
|
Source or destination character LOB (including NCLOBs) |
|
Source or destination character buffer (used with CLOBs and NCLOBs) |
|
Size of a buffer or LOB, offset into a LOB, or amount to access (in bytes for BLOBs or characters for CLOBs or NCLOBs) |
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 CLOBs enables the CLOB
type to accept a CLOB
or NCLOB
locator variable as input.
Rules and Limits
General Rules and Limits
-
The following rules apply in the specification of subprograms in this package:
-
The
newlen
,offset
, andamount
parameters for subprograms operating on BLOBs must be specified in terms of bytes. -
The
newlen
,offset
, andamount
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
, andnth
must not exceed the valueBLOBMAXSIZE
(for a BLOB) orCLOBMAXSIZE
(for a CLOB or NCLOB) in anyDBMS_LOB
subprogram. In TimesTen, the maximum BLOB size is 16 MB and the maximum CLOB or NCLOB size is 4 MB. -
For CLOBs consisting of fixed-width multibyte characters, the maximum value for these parameters must not exceed (
CLOBMAXSIZE
/character_width_in_bytes
) characters.
-
-
PL/SQL language specifications stipulate an upper limit of 32767 bytes (not characters) for
RAW
andVARCHAR2
parameters used inDBMS_LOB
subprograms. For example, if you declare a variable as follows:charbuf VARCHAR2(3000)
Then
charbuf
can hold 3000 single byte characters or 1500 two-byte fixed width characters. This has an important consequence forDBMS_LOB
subprograms for CLOBs and NCLOBs. -
The
%CHARSET
clause indicates that the form of the parameter with%CHARSET
must match the form of theANY_CS
parameter to which it refers.For example, in
DBMS_LOB
subprograms that take aVARCHAR2
buffer parameter, the form of theVARCHAR2
buffer must be appropriate for the form of the character LOB parameter. If the specified LOB is of typeNCLOB
, the buffer must containNCHAR
data. If the specified LOB is of typeCLOB
, the buffer must containCHAR
data.For
DBMS_LOB
subprograms that take two-character LOB parameters, both parameters must have the same form. That is, they must both be NCLOBs or they must both be CLOBs. -
If the value of
amount
plusoffset
exceeds the maximum LOB size allowed by the database, then access exceptions are raised. In TimesTen, the maximum BLOB size is 16 MB and the maximum CLOB or NCLOB size is 4 MB.Under these input conditions, subprograms such as
READ
,COMPARE
,INSTR
, andSUBSTR
read until the end of the LOB is reached. For example, for aREAD
operation on a BLOB, if the user specifies anoffset
value of 3 MB and an amount value of 2 MB on a LOB that is 4 MB, thenREAD
returns only 1 MB (4 MB minus 3 MB). -
Functions with
NULL
or invalid input values for parameters returnNULL
. Procedures withNULL
values for destination LOB parameters raise exceptions. -
Operations involving patterns as parameters, such as
COMPARE
,INSTR
, andSUBSTR
, do not support regular expressions or special matching characters (such as%
in theLIKE
operator in SQL) in thepattern
parameter or substrings. -
The end-of-LOB condition is indicated by the
READ
procedure using aNO_DATA_FOUND
exception. This exception is raised only upon an attempt by the user to read beyond the end of the LOB. TheREAD
buffer for the last read contains 0 bytes. -
Unless otherwise stated, the default value for an
offset
parameter is 1, which indicates the first byte in the BLOB data or the first character in the CLOB or NCLOB data. No default values are specified for theamount
parameter. You must input the values explicitly. -
You must lock the row containing the destination LOB before calling any subprograms that modify the LOB, such as
APPEND
,COPY
,ERASE
,TRIM
, orWRITE
. These subprograms do not implicitly lock the row containing the LOB.
Maximum LOB Size
The maximum size for LOBs in TimesTen is 16 MB for BLOBs and 4 MB for CLOBs or NCLOBs.
Maximum Buffer Size
The maximum buffer size is 32767 bytes.
For BLOBs, where buffer size is expressed in bytes, the number of bytes cannot exceed 32767.
For CLOBs or NCLOBs, where buffer size is expressed in characters, the number of characters cannot result in a buffer larger than 32767 bytes. For example, if you are using fixed-width, two-byte characters, then specifying 20000 characters is an error (20000*2 = 40000, which is greater than 32767).
Operational Notes
This section discusses how to use LOBS and the DBMS_LOB
package, covering these topics:
LOB Usage Notes
DBMS_LOB
subprograms operate based on LOB locators. For the successful completion of DBMS_LOB
subprograms, you must provide an input locator that represents a LOB, either a temporary LOB (discussed below) or a persistent LOB that already exists in the database tablespaces.
Tip:
-
In TimesTen, LOB locators do not remain valid past the end of the transaction.
-
LOB manipulations through APIs that use LOB locators result in usage of TimesTen temporary space. Any significant number of such manipulations may necessitate a size increase for the TimesTen temporary data partition. See TempSize in Oracle TimesTen In-Memory Database Reference.
To use LOBs in your database, you must first use SQL data definition language (DDL) to define the tables that contain columns of type BLOB
, CLOB
, or NCLOB
.
In TimesTen, you can write data into the middle of a LOB only by overwriting previous data. There is no functionality to insert data into the middle of a LOB and move previous data, beginning at that point, toward the end of the LOB correspondingly. Similarly, in TimesTen you can delete data from the middle of a LOB only by overwriting previous data with zeros or null data. There is no functionality to remove data from the middle of a LOB and move previous data, beginning at that point, toward the beginning of the LOB correspondingly. In either case in TimesTen, the size of the LOB does not change, except in the circumstance where from the specified offset there is less space available in the LOB than there is data to write. (In Oracle Database you can use the DBMS_LOB
FRAGMENT
procedures to insert or delete data, move other data accordingly, and change the size of the LOB. TimesTen does not support those procedures.)
DBMS_LOB
procedures and functions are supported for both TimesTen LOBs and passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen and exposed as TimesTen LOBs. Note, however, that CREATETEMPORARY
can only be used to create a temporary LOB in TimesTen. If a temporary passthrough LOB is created using some other mechanism, such as SQL, ISTEMPORARY
and FREETEMPORARY
can be used on that LOB.
TimesTen does not support DBMS_LOB
subprograms intended specifically for BFILEs, SecureFiles (including Database File System features), or inserting or deleting data fragments in the middle of a LOB (FRAGMENT
subprograms).
Persistent LOBs
To populate your database table with LOBs after BLOB
, CLOB
, or NCLOB
columns are defined in the table, use the SQL data manipulation language (DML) to initialize or populate the locators in the LOB columns.
Temporary LOBs
TimesTen supports the definition, creation, deletion, access, and update of temporary LOBs. The temporary data partition stores the temporary LOB data. Temporary LOBs are not permanently stored in the database. Their primary purpose is for performing transformations on LOB data from applications.
You can use PL/SQL to create or manipulate a temporary LOB (BLOB, CLOB, or NCLOB).
A temporary LOB is empty when it is created. In TimesTen, all temporary LOBs are deleted at the end of the transaction in which they were created. Also, if a process dies unexpectedly or if the database crashes, temporary LOBs are deleted and the space for temporary LOBs is freed.
There is no support for consistent-read, undo, backup, parallel processing, or transaction management for temporary LOBs. Because consistent-read and rollbacks are not supported for temporary LOBs, you must free the temporary LOB and start over again if you encounter an error.
In PL/SQL, do not use more than one locator for a temporary LOB. Because consistent-read, 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 temporary LOB locator can be passed by reference to other procedures if necessary.
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 persistent LOBs in these types of situations, because consistent-read snapshots and version pages enable users to see their own versions of the LOB cheaply.
Because temporary LOBs are not associated with any table schema, there are no meanings to the terms in-row and out-of-row for 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, as well as other programmatic interfaces, operates on temporary LOBs through these locators just as they do for persistent LOBs.
There is no concept of client-side temporary LOBs. All temporary LOBs reside in the server.
A temporary LOB instance can be accessed and modified using appropriate DBMS_LOB
functions and procedures, just as for persistent LOBs. To make a temporary LOB persistent, you can use the COPY
procedure to copy it into a BLOB
, CLOB
, or NCLOB
column (as appropriate) in the database.
When you are finished with a temporary LOB instance, use the FREETEMPORARY
procedure to free it.
Security is provided through the LOB locator. Only the user who created the temporary LOB can see it. Locators cannot be passed from one user 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.
The following notes are specific to temporary LOBs:
-
All functions in
DBMS_LOB
returnNULL
if any parameter isNULL
. All procedures inDBMS_LOB
raise an exception if the LOB locator is input asNULL
. -
Operations based on CLOBs do not verify whether the character set IDs (CSIDs) of the parameters (
CLOB
parameters,VARCHAR2
buffers and patterns, and so on) match. It is the user's responsibility to ensure this.
Exceptions
Table 2-3 DBMS_LOB Exceptions
Exception | Code | Description |
---|---|---|
|
|
There was an attempt to write too much data to the LOB. In TimesTen, BLOB size is limited to 16 MB and CLOB or NCLOB size is limited to 4 MB. |
|
|
Cannot perform operation with LOB buffering enabled. |
|
|
The length of the |
|
|
The length of the |
|
|
The argument is expecting a valid non-null value but the argument value passed in is |
|
|
This is the end-of-LOB indicator for looping read operations. It is not a hard error. |
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
|
This is a PL/SQL error for invalid values to subprogram parameters. |
DBMS_LOB Subprograms
Table 2-4 summarizes the DBMS_LOB
subprograms, followed by a full description of each subprogram.
Table 2-4 DBMS_LOB Subprograms
Subprogram | Description |
---|---|
Appends the contents of the source LOB to the destination LOB. |
|
Closes a previously opened LOB. |
|
Compares two entire LOBs or parts of two LOBs. |
|
Reads character data from a source CLOB or NCLOB, converts the character data to the specified character set, writes the converted data to a destination BLOB in binary format, and returns the new offsets. |
|
Takes a source BLOB, converts the binary data in the source to character data using the specified character set, writes the character data to a destination CLOB or NCLOB, and returns the new offsets. |
|
Copies all or part of the source LOB to the destination LOB. |
|
Creates a temporary LOB in the temporary data partition. |
|
Erases all or part of a LOB. |
|
Frees a temporary LOB in the temporary data partition. |
|
Returns the amount of space used in the LOB chunk to store the LOB value. |
|
Returns the length of the LOB value, in bytes for a BLOB or characters for a CLOB. |
|
Returns the storage limit for the LOB type of the specified LOB. |
|
Returns the matching position of the |
|
Checks to see if the LOB was already opened using the input locator. |
|
Checks if the locator is pointing to a temporary LOB. |
|
Opens a LOB (persistent or temporary) in the indicated mode, read/write or read-only. |
|
Reads data from the LOB starting at the specified offset. |
|
Returns part of the LOB value starting at the specified offset. |
|
Trims the LOB value to the specified length. |
|
Writes data to the LOB from a specified offset. |
|
Appends a buffer to the end of a LOB. |
APPEND Procedures
This procedure appends the contents of a source LOB to a destination LOB. It appends the complete source LOB. (Do not confuse this with the WRITEAPPEND
procedure.)
Note:
Also see WRITEAPPEND 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 2-5 APPEND Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB to which the data is being appended |
|
Locator for the LOB from which the data is being read |
Usage Notes
-
It is recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. If you opened the LOB before performing the operation, however, you must close it before you commit or roll back the transaction.
Exceptions
Table 2-6 APPEND Procedure Exceptions
Exception | Description |
---|---|
|
Either the source or destination LOB is |
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform operation if LOB buffering is enabled on either LOB. |
Examples
This example shows use of the APPEND
procedure.
create table t1 (a int, c clob);
insert into t1(a,c) values(1, 'abcde');
1 row inserted.
commit;
declare
c1 clob;
c2 clob;
begin
c1 := 'abc';
select c into c2 from t1 where a = 1;
dbms_output.put_line('c1 before append is ' || c1);
dbms_output.put_line('c2 before append is ' || c2);
dbms_lob.append(c1, c2);
dbms_output.put_line('c1 after append is ' || c1);
dbms_output.put_line('c2 after append is ' || c2);
insert into t1 values (2, c1);
end;
c1 before append is abc
c2 before append is abcde
c1 after append is abcabcde
c2 after append is abcde
PL/SQL procedure successfully completed.
select * from t1;
< 1, abcde >
< 2, abcabcde >
2 rows found.
(Output is shown after running the commands from a SQL script.)
CLOSE Procedures
This procedure closes a previously opened LOB.
Syntax
DBMS_LOB.CLOSE (
lob_loc IN OUT NOCOPY BLOB);
DBMS_LOB.CLOSE (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
Parameters
Table 2-7 CLOSE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
Usage Notes
-
CLOSE
requires a round-trip to the server. -
It is not mandatory that you wrap LOB operations inside
OPEN
andCLOSE
calls. However, if you open a LOB, you must close it before you commit or roll back the transaction. -
It is an error to commit the transaction before closing all LOBs that were opened by the transaction. When the error is returned, the "open" status 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.
Exceptions
An error is returned if the LOB is not open.
COMPARE Functions
This 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 := DBMS_LOB.BLOBMAXSIZE,
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 := DBMS_LOB.CLOBMAXSIZE,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
Parameters
Table 2-8 COMPARE Function Parameters
Parameter | Description |
---|---|
|
Locator for the first LOB for comparison |
|
Locator for the second LOB for comparison |
|
Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to compare |
|
Offset in bytes or characters in the first LOB (starting from 1) |
|
Offset in bytes or characters in the second LOB (starting from 1) |
Return Values
The function returns one of the following:
-
0 (zero) if the data matches exactly over the specified range
-
-1 if the first LOB is less than the second
-
1 if the first LOB is greater than the second
-
NULL
ifamount
,offset_1
, oroffset_2
is an invalid value, outside the range 1 toBLOBMAXSIZE
orCLOBMAXSIZE
(as appropriate), inclusive
Usage Notes
-
You can only compare LOBs of the same type. For example, you cannot compare a BLOB to a CLOB.
-
For fixed-width
n
-byte CLOBs or NCLOBs, if the input amount forCOMPARE
is specified to be greater thanCLOBMAXSIZE/
n
, thenCOMPARE
matches characters in a range of size that is eitherCLOBMAXSIZE/
n
orMax(length(clob1), length(clob2))
, whichever is less.
CONVERTTOBLOB Procedure
This procedure reads character data from a source CLOB or NCLOB, converts the character data to the character set you specify, writes the converted data to a destination BLOB in binary format, and returns the new offsets. You can use this procedure with any combination of persistent or temporary LOBs.
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 2-9 CONVERTTOBLOB Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the destination LOB |
|
Locator for the source LOB |
|
Number of characters to convert from the source LOB If you want to convert the entire CLOB or NCLOB, pass the constant |
|
Specify a value of 1 to start at the beginning of the LOB.
|
|
|
|
Character set ID for the converted BLOB data |
|
This parameter is not supported by TimesTen. |
|
Warning message This parameter is not supported by TimesTen. |
Usage Notes
This section discusses special usage notes for CONVERTTOBLOB
.
Preconditions
Before a call to CONVERTTOBLOB
, the following preconditions must be met.
-
Both the source and destination LOBs 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 theSELECT
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 2-10 gives a summary of typical values for each parameter. Note that constants are used for some values. These constants are defined in the dbmslob.sql
package specification file.
Table 2-10 CONVERTTOBLOB Typical Values
Parameter | Value | Description |
---|---|---|
|
|
Convert the entire LOB. |
|
|
Start from the beginning. |
|
|
Start from the beginning. |
|
|
Default character set ID, use same ID as source CLOB. |
|
|
This is the default language context (ignored by TimesTen). |
|
|
This is a warning message (ignored by TimesTen). |
General Notes
-
You must specify the desired character set ID for the destination BLOB in the
blob_csid
parameter. If you pass a zero value, the database assumes that the desired character set is the same as the source CLOB character set. -
You must specify the offsets for both the source and destination LOBs, and the number of characters to copy from the source LOB. The
amount
andsrc_offset
values are in characters and thedest_offset
is in bytes. To convert the entire LOB, you can specifyCLOBMAXSIZE
for theamount
parameter. -
CONVERTTOBLOB
gets the source and destination LOBs as necessary before converting and writing the data.
Exceptions
Table 2-11 CONVERTTOBLOB Procedure Exceptions
Exception | Description |
---|---|
|
Any of the input parameters is |
|
Any of the following is true:
|
CONVERTTOCLOB Procedure
This procedure takes a source BLOB, converts the binary data in the source to character data using the character set you specify, writes the character data to a destination CLOB or NCLOB, and returns the new offsets. You can use this procedure with any combination of persistent or temporary LOBs.
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 2-12 CONVERTTOCLOB Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the destination LOB |
|
Locator for the source LOB |
|
Number of bytes to convert from the source LOB If you want to convert the entire BLOB, pass the constant |
|
Specify a value of 1 to start at the beginning of the LOB.
This offset always points to the beginning of the first complete character after the end of the write. |
|
|
|
Character set ID for the source BLOB data |
|
This parameter is not supported by TimesTen. |
|
Warning message This parameter is not supported by TimesTen. |
Usage Notes
This section discusses special usage notes for CONVERTTOCLOB
.
Preconditions
Before a call to CONVERTTOCLOB
, the following preconditions must be met.
-
Both the source and destination LOBs 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 theFOR UPDATE
clause of theSELECT
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 2-13 gives a summary of typical values for each parameter. Note that constants are used for some values. These constants are defined in the dbmslob.sql
package specification file.
Table 2-13 CONVERTTOCLOB Typical Values
Parameter | Value | Description |
---|---|---|
|
|
Convert the entire LOB. |
|
|
Start from the beginning. |
|
|
Start from the beginning. |
|
|
Default character set ID, use same ID as destination CLOB. |
|
|
This is the default language context (ignored by TimesTen). |
|
|
This is a warning message (ignored by TimesTen). |
General Notes
-
You must specify the desired character set ID for the source BLOB in the
blob_csid
parameter. If you pass a zero value, the database assumes that the desired character set is the same as the destination CLOB character set. -
You must specify the offsets for both the source and destination LOBs, and the number of characters to copy from the source LOB. The
amount
andsrc_offset
values are in bytes and thedest_offset
is in characters. To convert the entire LOB, you can specifyBLOBMAXSIZE
for theamount
parameter. -
CONVERTTOCLOB
gets the source and destination LOBs as necessary before converting and writing the data.
Exceptions
Table 2-14 CONVERTTOCLOB Procedure Exceptions
Exception | Description |
---|---|
|
Any of the input parameters is |
|
Any of the following is true:
|
COPY Procedures
This procedure copies all or part of a source LOB to a destination 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 2-15 COPY Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the destination LOB being copied to |
|
Locator for the source LOB being copied from |
|
Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to copy |
|
Offset in bytes or characters in the destination LOB for the start of the copy (starting from 1) |
|
Offset in bytes or characters in the source LOB for the start of the copy (starting from 1) |
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 (for BLOBs) or spaces (for CLOBs or NCLOBs) are inserted in the destination LOB to reach the offset. 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 recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction. -
In addition to copying from one TimesTen LOB to another,
COPY
can copy from a TimesTen LOB to a passthrough LOB, from a passthrough LOB to a TimesTen LOB, or from one passthrough LOB to another passthrough LOB. An attempt to copy a passthrough LOB to a TimesTen LOB when the passthrough LOB is larger than the TimesTen LOB size limit results in an error.
Exceptions
Maximum LOB size is BLOBMAXSIZE
for a BLOB or CLOBMAXSIZE
for a CLOB.
Table 2-16 COPY Procedure Exceptions
Exception | Description |
---|---|
|
Any of the input parameters is |
|
Any of the following is true:
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform the operation if LOB buffering is enabled on either LOB. |
Examples
The examples in this section show how to copy LOBs in PL/SQL, copying between passthrough
LOBs (from Oracle Database) and TimesTen LOBs. The first example uses the
COPY
procedure. The second, as contrast, simply uses
INSERT
and UPDATE
statements, though also uses
functionality of the DBMS_LOB
package.
Copy CLOBs Using COPY Procedure
This example uses the COPY
procedure to first copy a passthrough CLOB from
Oracle Database into a TimesTen CLOB, then to copy a TimesTen CLOB into a passthrough
CLOB.
autocommit 0;
passthrough 0;
DROP TABLE tt_table; CREATE TABLE tt_table (i INT, c CLOB); COMMIT;
passthrough 3;
DROP TABLE ora_table; CREATE TABLE ora_table (i INT, c CLOB); COMMIT;
passthrough 0;
set serveroutput on;
DECLARE
passthru_clob CLOB;
tt_clob CLOB;
clob_length BINARY_INTEGER;
clob_buffer VARCHAR2(80);
BEGIN
EXECUTE IMMEDIATE 'call ttoptsetflag(''passthrough'', 1)';
-- Note that in PL/SQL, passthrough statements must be executed as
-- dynamic SQL, and SELECT INTO must be used to assign a passthrough LOB.
-- 1. Copy a passthrough CLOB on Oracle Database to a TimesTen CLOB
-- On Oracle Database : insert a row with an empty CLOB, get a passthrough CLOB
-- handle, and append to the passthrough CLOB.
EXECUTE IMMEDIATE 'INSERT INTO ora_table VALUES (1, EMPTY_CLOB())';
EXECUTE IMMEDIATE 'SELECT c FROM ora_table WHERE i = 1 FOR UPDATE'
INTO passthru_clob;
DBMS_LOB.APPEND(passthru_clob, 'Copy from Oracle Database to TimesTen');
clob_length := DBMS_LOB.GETLENGTH(passthru_clob);
-- On TimesTen: insert a row with an empty CLOB, and get a TimesTen CLOB handle
INSERT INTO tt_table VALUES (1, EMPTY_CLOB()) RETURNING c INTO tt_clob;
-- Copy the passthrough CLOB on Oracle Database to a TimesTen CLOB
DBMS_LOB.COPY(tt_clob, passthru_clob, clob_length, 1, 1);
-- On TimesTen: display the modified TimesTen CLOB
DBMS_LOB.READ(tt_clob, clob_length, 1, clob_buffer);
DBMS_OUTPUT.PUT_LINE(clob_buffer);
-- 2. Copy a TimesTen CLOB to a passthrough CLOB on Oracle Database
-- On TimesTen: insert a row with LOB data, and get a TimesTen CLOB handle
INSERT INTO tt_table VALUES (2, 'Copy from TimesTen to Oracle Database.')
RETURNING c INTO tt_clob;
clob_length := DBMS_LOB.GETLENGTH(tt_clob);
-- On Oracle Database: insert a row with an empty CLOB, and get a passthrough
-- CLOB handle
EXECUTE IMMEDIATE 'INSERT INTO ora_table VALUES (2, EMPTY_CLOB())';
EXECUTE IMMEDIATE 'SELECT c FROM ora_table WHERE i = 2 FOR UPDATE'
INTO passthru_clob ;
-- Copy a TimesTen CLOB to a passthrough CLOB on Oracle Database
DBMS_LOB.COPY(passthru_clob, tt_clob, clob_length, 1, 1);
-- On Oracle Database: display the modified passthrough CLOB
DBMS_LOB.READ(passthru_clob, clob_length, 1, clob_buffer);
DBMS_OUTPUT.PUT_LINE(clob_buffer);
COMMIT;
EXECUTE IMMEDIATE 'call ttoptsetflag(''passthrough'', 0)';
END;
Copy CLOBs Using INSERT and UPDATE Statements
A passthrough LOB from Oracle Database can be bound to an INSERT
or
UPDATE
statement executed against a table in TimesTen. You can copy a
passthrough LOB to a TimesTen LOB in this way. Similarly, a TimesTen LOB can be bound to a
passthrough INSERT
or UPDATE
statement executed against a
table in Oracle Database. You can copy a TimesTen LOB to a passthrough LOB in this way.
This example shows both of these scenarios.
autocommit 0;
passthrough 0;
DROP TABLE tt_table; CREATE TABLE tt_table (i INT, c CLOB); COMMIT;
passthrough 3;
DROP TABLE ora_table; CREATE TABLE ora_table (i INT, c CLOB); COMMIT;
passthrough 0;
set serveroutput on;
DECLARE
passthru_clob CLOB;
tt_clob CLOB;
clob_length BINARY_INTEGER;
clob_buffer VARCHAR2(80);
BEGIN
EXECUTE IMMEDIATE 'call ttoptsetflag(''passthrough'', 1)';
-- Note that in PL/SQL, passthrough statements must be executed as
-- dynamic SQL, and SELECT INTO must be used to assign a passthrough LOB.
-- 1. A TimesTen CLOB is updated with a passthrough CLOB on Oracle Database
-- On TimesTen: insert a row with a NULL CLOB value
INSERT INTO tt_table VALUES (1, NULL);
-- On Oracle Database: insert a row with an empty CLOB, get a passthrough CLOB
-- handle
EXECUTE IMMEDIATE 'INSERT INTO ora_table
VALUES (1, ''Copy from Oracle Database to TimesTen'')';
EXECUTE IMMEDIATE 'SELECT c FROM ora_table WHERE i = 1' INTO passthru_clob ;
-- On TimesTen: update the TimesTen CLOB with the passthrough CLOB
UPDATE tt_table SET c = passthru_clob where i = 1;
-- On TimesTen: display the modified TimesTen CLOB
SELECT c INTO tt_clob FROM tt_table WHERE i = 1;
clob_length := DBMS_LOB.GETLENGTH(tt_clob);
DBMS_LOB.READ(tt_clob, clob_length, 1, clob_buffer);
DBMS_OUTPUT.PUT_LINE(clob_buffer);
-- 2. A passthrough table on Oracle Database is inserted with a TimesTen CLOB
-- On TimesTen: insert a row with a CLOB value, and get a TimesTen CLOB handle
INSERT INTO tt_table VALUES (2, 'Copy from TimesTen to Oracle Database.')
RETURNING c INTO tt_clob;
-- On Oracle Database: insert a row on Oracle Database with the TimesTen CLOB
EXECUTE IMMEDIATE 'INSERT INTO ora_table VALUES (2, :1)' USING tt_clob;
-- On Oracle Database: display the modified passthrough CLOB
EXECUTE IMMEDIATE 'SELECT c FROM ora_table WHERE i = 2' INTO passthru_clob;
clob_length := DBMS_LOB.GETLENGTH(passthru_clob);
DBMS_LOB.READ(passthru_clob, clob_length, 1, clob_buffer);
DBMS_OUTPUT.PUT_LINE(clob_buffer);
COMMIT;
EXECUTE IMMEDIATE 'call ttoptsetflag(''passthrough'', 0)';
END;
CREATETEMPORARY Procedures
This procedure creates a temporary BLOB, CLOB, or NCLOB in the temporary data partition.
Use FREETEMPORARY Procedures when you are finished using temporary LOBs.
Tip:
In TimesTen, creation of a temporary LOB results in creation of a database transaction if one is not already in progress. You must execute a commit or rollback to close the transaction.
Syntax
DBMS_LOB.CREATETEMPORARY (
lob_loc IN OUT NOCOPY BLOB,
cache IN BOOLEAN,
dur IN BINARY_INTEGER := DBMS_LOB.SESSION);
DBMS_LOB.CREATETEMPORARY (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
cache IN BOOLEAN,
dur IN BINARY_INTEGER := DBMS_LOB.SESSION);
Parameters
Table 2-17 CREATETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the temporary LOB It is permissible to specify an |
|
Flag indicating whether the LOB should be read into buffer cache |
|
One of two predefined duration values— Note: Either setting is permitted, but in TimesTen the duration of a LOB locator does not extend past the end of the transaction. |
Usage Notes
-
CREATETEMPORARY
cannot be used to create a temporary passthrough LOB.
ERASE Procedures
This procedure erases all or part of a LOB.
Note:
Also see TRIM Procedures.
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 2-18 ERASE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
( ( |
|
Absolute offset (starting from 1) from the beginning of the LOB, in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) |
Usage Notes
-
When data is erased from the middle of a LOB, zero-byte fillers (for BLOBs) or spaces (for CLOBs or NCLOBs) are written.
-
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 data is reached first. The actual number of characters or bytes erased is returned in theamount
parameter. -
It is recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction.
Note:
The length of the LOB does not decrease when a section of the LOB is erased. To decrease the length of a LOB, see TRIM Procedures.
Exceptions
Maximum LOB size is BLOBMAXSIZE
for a BLOB or CLOBMAXSIZE
for a CLOB.
Table 2-19 ERASE Procedure Exceptions
Exception | Description |
---|---|
|
Any input parameter is |
|
Any of the following is true:
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform operation if LOB buffering is enabled on the LOB. |
FREETEMPORARY Procedures
This procedure frees a temporary BLOB, CLOB, or NCLOB in the temporary data partition.
Also refer to CREATETEMPORARY Procedures.
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 2-20 FREETEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
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 through an assignment operation in PL/SQL, then the target of the assignment is also freed and marked as invalid.
-
CREATETEMPORARY
cannot be used to create a temporary passthrough LOB; however, if one is created using some other mechanism, such as SQL,ISTEMPORARY
andFREETEMPORARY
can be used on that LOB.
GETCHUNKSIZE Functions
In TimesTen, this function is not supported and simply returns the value 32K for interoperability. This value is not relevant for any performance tuning for a TimesTen application.
Refer to GETCHUNKSIZE Functions in Oracle Database PL/SQL Packages and Types Reference if you are interested in Oracle Database functionality.
Syntax
DBMS_LOB.GETCHUNKSIZE (
lob_loc IN BLOB)
RETURN INTEGER;
DBMS_LOB.GETCHUNKSIZE (
lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
Parameters
Table 2-21 GETCHUNKSIZE Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
Return Values
Returns the value 32K, but applications should not rely on this number for performance tuning.
Exceptions
Table 2-22 GETCHUNKSIZE Procedure Exceptions
Exception | Description |
---|---|
|
Cannot perform operation if LOB buffering is enabled on the LOB. |
GETLENGTH Functions
This function returns the length of the specified LOB in bytes (for BLOBs) or characters (for CLOBs or NCLOBs).
Syntax
DBMS_LOB.GETLENGTH (
lob_loc IN BLOB)
RETURN INTEGER;
DBMS_LOB.GETLENGTH (
lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
Parameters
Table 2-23 GETLENGTH Function Parameter
Parameter | Description |
---|---|
|
Locator for the LOB |
Return Values
Returns an INTEGER
value for the length of the LOB in bytes or characters. NULL
is returned if the value of the input LOB or lob_loc
is NULL
.
Usage Notes
-
Any zero-byte or space filler in the LOB caused by previous
ERASE
orWRITE
operations is included in the length count. The length of an empty LOB is 0 (zero).
Exceptions
Table 2-24 GETLENGTH Procedure Exceptions
Exception | Description |
---|---|
|
Cannot perform operation if LOB buffering is enabled on the LOB. |
Examples
The following example shows use of the GETLENGTH
function.
create table t1 (a int, b blob, c clob);
insert into t1(a,b,c) values(1, 0x123451234554321, 'abcde');
1 row inserted.
commit;
declare
myblob blob;
i integer;
begin
myblob := empty_blob();
i := dbms_lob.getlength(myblob);
dbms_output.put_line('Length of BLOB before SELECT: ' || i);
select b into myblob from t1 where a=1;
i := dbms_lob.getlength(myblob);
dbms_output.put_line('Length of BLOB after SELECT: ' || i);
end;
Length of BLOB before SELECT: 0
Length of BLOB after SELECT: 8
PL/SQL procedure successfully completed.
(Output is shown after running the commands from a SQL script.)
GET_STORAGE_LIMIT Functions
This function returns the storage limit, in bytes, for the type of specified LOB.
Syntax
DBMS_LOB.GET_STORAGE_LIMIT (
lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
DBMS_LOB.GET_STORAGE_LIMIT (
lob_loc IN BLOB)
RETURN INTEGER;
Parameters
Table 2-25 GET_STORAGE_LIMIT Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
Return Value
In TimesTen, the value returned is simply the maximum storage space, in bytes, for the type of specified LOB. That is 16777216 (16 MB) for a BLOB or 4194304 (4 MB) for a CLOB or NCLOB.
INSTR Functions
This function returns the matching position of the n
th occurrence of a specified pattern in a specified LOB, starting from a specified offset.
Note:
Also see SUBSTR Functions.
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;
Parameters
Table 2-26 INSTR Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Pattern to be tested for The pattern is in |
|
Absolute offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs), starting from 1, at which the pattern-matching is to start |
|
Occurrence number of the pattern in the LOB, starting from 1 |
Return Values
The function returns one of the following:
-
An
INTEGER
value for the offset of the beginning of the matched pattern, in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) -
0 (zero) if the pattern is not found
-
NULL
if any of the input parameters isNULL
or invalid or any of the following is true:-
offset
< 1 oroffset
> maximum LOB size -
nth
< 1 ornth
> maximum LOB size
Where maximum LOB size is
BLOBMAXSIZE
for a BLOB orCLOBMAXSIZE
for a CLOB. -
Usage Notes
-
For a CLOB or NCLOB, the form of the
VARCHAR2
buffer for thepattern
parameter must be appropriate for the type of LOB. If the specified LOB is of typeNCLOB
, the pattern must containNCHAR
data. If the specified LOB is of typeCLOB
, the pattern must containCHAR
data. -
Operations that accept
RAW
orVARCHAR2
parameters for pattern matching, such asINSTR
, do not support regular expressions or special matching characters (as with SQLLIKE
) in the pattern parameter or substrings.
ISOPEN Functions
This function checks to see if a LOB was already opened using the input locator.
Syntax
DBMS_LOB.ISOPEN (
lob_loc IN BLOB)
RETURN INTEGER;
DBMS_LOB.ISOPEN (
lob_loc IN CLOB CHARACTER SET ANY_CS)
RETURN INTEGER;
Parameters
Table 2-27 ISOPEN Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
Return Values
The return value is 1 if the LOB is open, or 0 (zero) if not.
Usage Notes
-
The "open" status is associated with the LOB, not with the locator. If any locator is used in opening the LOB, then any other locator for the LOB would also see it as open.
-
ISOPEN
requires a round-trip, because it must check the state on the server to see if the LOB is open.
ISTEMPORARY Functions
This function determines whether a LOB 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;
Parameters
Table 2-28 ISTEMPORARY Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
Return Values
The return value is 1 if the LOB exists and is temporary, 0 (zero) if the LOB does not exist or is not temporary, or NULL
if the given locator value is NULL
.
Usage Notes
-
When you free a temporary LOB with
FREETEMPORARY
, the LOB locator is not set toNULL
. Consequently,ISTEMPORARY
returns 0 (zero) for a locator that has been freed but not explicitly reset toNULL
. -
CREATETEMPORARY
cannot be used to create a temporary passthrough LOB; however, if one is created using some other mechanism, such as SQL,ISTEMPORARY
andFREETEMPORARY
can be used on that LOB.
OPEN Procedures
This procedure opens a LOB in the indicated mode, read-only or 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);
Parameters
Table 2-29 OPEN Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Mode in which to open, either |
Usage Notes
-
An error is returned if you try to write to a LOB that was opened as read-only.
-
OPEN
requires a round-trip to the server and causes execution of other code that relies on theOPEN
call. -
It is not mandatory that you wrap LOB operations inside
OPEN
andCLOSE
calls. However, if you open a LOB, you must close it before you commit or roll back the transaction. -
It is an error to commit the transaction before closing all LOBs that were opened in the transaction. When the error is returned, the "open" status of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to both LOB and non-LOB data in the transaction are committed.
READ Procedures
This procedure reads part of a LOB, starting from a specified absolute offset from the beginning of the LOB, and returns the specified number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) into the buffer
parameter.
Syntax
DBMS_LOB.READ (
lob_loc IN BLOB,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT RAW);
DBMS_LOB.READ (
lob_loc IN CLOB CHARACTER SET ANY_CS,
amount IN OUT NOCOPY INTEGER,
offset IN INTEGER,
buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Parameters
Table 2-30 READ Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
( ( |
|
Offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) from the start of the LOB (starting from 1) |
|
Output buffer from the read operation |
Usage Notes
-
If the input
offset
points past the end of the LOB, thenamount
is set to 0 (zero) and aNO_DATA_FOUND
exception is raised. -
For a CLOB or NCLOB, the form of the
VARCHAR2
buffer for thebuffer
parameter must be appropriate for the type of LOB. If the specified LOB is of typeNCLOB
, the buffer must containNCHAR
data. If the specified LOB is of typeCLOB
, the buffer must containCHAR
data. -
When calling
READ
from a client, the returned buffer contains data in the client character set. The database converts the LOB value from the server character set to the client character set before it returns the buffer to the user. -
READ
gets the LOB, if necessary, before the read.
Exceptions
Maximum LOB size is BLOBMAXSIZE
for a BLOB or CLOBMAXSIZE
for a CLOB.
Table 2-31 READ Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Any of the following is true:
|
|
The end of the LOB is reached and there are no more bytes or characters to read from the LOB. The |
SUBSTR Functions
This function returns a specified number of bytes (for a BLOB) or characters (for a CLOB or NCLOB), starting at a specified offset from the beginning of a specified LOB.
Note:
Also see INSTR Functions and READ Procedures.
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;
Parameters
Table 2-32 SUBSTR Function Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to read |
|
Offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) from the start of the LOB (starting from 1) |
Return Values
Returns one of the following:
-
RAW
bytes from a BLOB -
VARCHAR2
characters from a CLOB or NCLOB -
NULL
if any input parameter isNULL
or any of the following is true:-
amount
< 1 oramount
> 32767 bytes (or the character equivalent) -
offset
< 1 oroffset
> maximum LOB size
Where maximum LOB size is
BLOBMAXSIZE
for a BLOB orCLOBMAXSIZE
for a CLOB. -
Usage Notes
-
For fixed-width
n
-byte CLOBs or NCLOBs, if the input amount forSUBSTR
is greater than (32767/n
), thenSUBSTR
returns a character buffer of length (32767/n
) or the length of the CLOB, whichever is less. For CLOBs in a varying-width character set,n
is the maximum byte-width used for characters in the CLOB. -
For a CLOB or NCLOB, the form of the
VARCHAR2
return buffer must be appropriate for the type of LOB. If the specified LOB is of typeNCLOB
, the buffer must containNCHAR
data. If the specified LOB is of typeCLOB
, the buffer must containCHAR
data. -
When calling
SUBSTR
from a client, the returned buffer contains data in the client character set. The database converts the LOB value from the server character set to the client character set before it returns the buffer to the user. -
SUBSTR
returns 8191 or more characters based on the characters stored in the LOB. If all characters are not returned because the character byte size exceeds the available buffer, the user should either callSUBSTR
with a new offset to read the remaining characters, or call the subprogram in a loop until all the data is extracted. -
SUBSTR
get s the LOB, if necessary, before reading.
TRIM Procedures
This procedure trims a LOB to the length you specify in the newlen
parameter. Specify the new desired data length in bytes for BLOBs or characters for CLOBs or NCLOBs.
Note:
Also see ERASE Procedures and WRITEAPPEND Procedures.
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 2-33 TRIM Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Desired trimmed length of the LOB value, in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) |
Usage Notes
-
If you attempt to trim an empty LOB, no action is taken and
TRIM
returns no error. -
If the new length that you specify in
newlen
is greater than the size of the LOB, an exception is raised. -
It is recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction. -
TRIM
gets the LOB, if necessary, before altering the length of the LOB, unless the new length specified is 0 (zero).
Exceptions
Maximum LOB size is BLOBMAXSIZE
for a BLOB or CLOBMAXSIZE
for a CLOB.
Table 2-34 TRIM Procedure Exceptions
Exception | Description |
---|---|
|
The |
|
Either of the following is true:
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform operation if LOB buffering enabled is enabled on the LOB. |
WRITE Procedures
This procedure writes a specified amount of data into a LOB, starting from a specified 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 from the offset through the length you specify.
Note:
Also see COPY Procedures and WRITEAPPEND Procedures.
Syntax
DBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY BLOB,
amount IN INTEGER,
offset IN INTEGER,
buffer IN RAW);
DBMS_LOB.WRITE (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount IN INTEGER,
offset IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Parameters
Table 2-35 WRITE Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to write |
|
Offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) from the start of the LOB for the write operation (starting from 1) |
|
Input buffer with data for the write |
Usage Notes
-
There is an error if the specified 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 LOB. If the offset you specify is beyond the end of the data currently in the LOB, then zero-byte fillers (for BLOBs) or spaces (for CLOBs or NCLOBs) are inserted into the LOB to reach the offset. -
For a CLOB or NCLOB, the form of the
VARCHAR2
buffer for thebuffer
parameter must be appropriate for the type of LOB. If the specified LOB is of typeNCLOB
, the buffer must containNCHAR
data. If the specified LOB is of typeCLOB
, the buffer must containCHAR
data. -
When calling
WRITE
from a client, the buffer must contain data in the client character set. The database converts the client-side buffer to the server character set before it writes the buffer data to the LOB. -
It is recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction. -
WRITE
gets the LOB, if necessary, before writing to it, unless the write is specified to overwrite the entire LOB.
Exceptions
Maximum LOB size is BLOBMAXSIZE
for a BLOB or CLOBMAXSIZE
for a CLOB.
Table 2-36 WRITE Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Any of the following is true:
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform operation if LOB buffering is enabled on the LOB. |
WRITEAPPEND Procedures
This procedure appends a specified amount of data to the end of a LOB. The data is written from the buffer
parameter. (Do not confuse this with the APPEND
procedure.)
Note:
Also see APPEND Procedures, COPY Procedures, and WRITE Procedures.
Syntax
DBMS_LOB.WRITEAPPEND (
lob_loc IN OUT NOCOPY BLOB,
amount IN INTEGER,
buffer IN RAW);
DBMS_LOB.WRITEAPPEND (
lob_loc IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
amount IN INTEGER,
buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Parameters
Table 2-37 WRITEAPPEND Procedure Parameters
Parameter | Description |
---|---|
|
Locator for the LOB |
|
Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to write |
|
Input buffer with data 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 the
amount
bytes or characters from the buffer are appended to the LOB. -
For a CLOB or NCLOB, the form of the
VARCHAR2
buffer for thebuffer
parameter must be appropriate for the type of LOB. If the specified LOB is of typeNCLOB
, the buffer must containNCHAR
data. If the specified LOB is of typeCLOB
, the buffer must containCHAR
data. -
When calling
WRITEAPPEND
from a client, the buffer must contain data in the client character set. The database converts the client-side buffer to the server character set before it writes the buffer data to the LOB. -
It is recommended that you enclose write operations to the LOB with
OPEN
andCLOSE
calls, but not mandatory. However, if you opened the LOB before performing the operation, you must close it before you commit or roll back the transaction. -
WRITEAPPEND
gets the LOB, if necessary, before appending to it.
Exceptions
Table 2-38 WRITEAPPEND Procedure Exceptions
Exception | Description |
---|---|
|
Any of |
|
Any of the following is true:
|
|
Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.) |
|
Cannot perform operation if LOB buffering is enabled on the LOB. |