Skip Headers
Oracle® TimesTen In-Memory Database PL/SQL Packages Reference
11g Release 2 (11.2.2)

E21645-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 DBMS_LOB

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:

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

For information about AUTHID, see "Definer's rights and invoker's rights" in Oracle TimesTen In-Memory Database PL/SQL Developer's 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

CALL

BINARY_INTEGER

12

Create the temporary LOB with call duration.

DEFAULT_CSID

INTEGER

0

This is the default character set ID.

DEFAULT_LANG_CTX

INTEGER

0

This is the default language context.

LOB_READONLY

BINARY_INTEGER

0

Open the specified LOB read-only.

LOB_READWRITE

BINARY_INTEGER

1

Open the specified LOB read/write.

BLOBMAXSIZE

INTEGER

16777216 (16 MB)

Set maximum size of a BLOB in bytes.

CLOBMAXSIZE

INTEGER

4194304 (4 MB)

Set maximum size of a CLOB in bytes.

NO_WARNING

INTEGER

0

Indicates success, no warning message.

SESSION

BINARY_INTEGER

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.

TRANSACTION

BINARY_INTEGER

11

Create the temporary LOB with transaction duration.

WARN_INCONVERTIBLE_CHAR

INTEGER

1

Used by the conversion functions to indicate there is an inconvertible character.


Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_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 and NUMBER(38) data types are also identical. This document uses INTEGER 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

BLOB

Source or destination binary LOB

RAW

Source or destination RAW buffer (used with BLOBs)

CLOB

Source or destination character LOB (including NCLOBs)

VARCHAR2

Source or destination character buffer (used with CLOBs and NCLOBs)

INTEGER

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, and amount parameters for subprograms operating on BLOBs must be specified in terms of bytes.

    • The newlen, 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):

    1. Only positive, absolute offsets from the beginning of LOB data are permitted. Negative offsets from the tail of the LOB are not permitted.

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

    3. The value of offset, amount, newlen, and nth must not exceed the value BLOBMAXSIZE (for a BLOB) or CLOBMAXSIZE (for a CLOB or NCLOB) in any DBMS_LOB subprogram. In TimesTen, the maximum BLOB size is 16 MB and the maximum CLOB or NCLOB size is 4 MB.

    4. 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 and VARCHAR2 parameters used in DBMS_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 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 be appropriate for the form of the character LOB parameter. If the specified LOB is of type NCLOB, the buffer must contain NCHAR data. If the specified LOB is of type CLOB, the buffer must contain CHAR 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 plus offset 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, and SUBSTR read until the end of the LOB is reached. For example, for a READ operation on a BLOB, if the user specifies an offset value of 3 MB and an amount value of 2 MB on a LOB that is 4 MB in size, then READ returns only 1 MB (4 MB minus 3 MB).

  • Functions with NULL or invalid input values for parameters return 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.

  • 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 the amount 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, or WRITE. 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

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.

Important:

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

The rest of this section discusses the following topics:

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 is able to 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 return NULL if any parameter is NULL. All procedures in DBMS_LOB raise an exception if the LOB locator is input as NULL.

  • 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

ACCESS_ERROR

22925

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.

BUFFERING_ENABLED

22279

Cannot perform operation with LOB buffering enabled.

CONTENTTYPE_TOOLONG

43859

The length of the contenttype string exceeds the defined maximum. Modify the length of the contenttype string and retry the operation.

CONTENTTYPEBUF_WRONG

43862

The length of the contenttype buffer is less than the defined constant. Modify the length of the contenttype buffer and retry the operation.

INVALID_ARGVAL

21560

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

NO_DATA_FOUND

1403

This is the end-of-LOB indicator for looping read operations. It is not a hard error.

QUERY_WRITE

14553

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

VALUE_ERROR

6502

This is a PL/SQL error for invalid values to subprogram parameters.


Note:

Several DBMS_LOB subprograms cannot operate if LOB buffering is enabled, as noted in the Exceptions subsections for those subprograms. (LOB buffering is a feature that can be enabled through OCI, for example. See "LOB Buffering Subsystem" in Oracle Database SecureFiles and Large Objects Developer's Guide.

Summary of DBMS_LOB subprograms

Table 2-4 DBMS_LOB subprograms

Subprogram Description

APPEND procedures

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

CLOSE procedures

Closes a previously opened LOB.

COMPARE functions

Compares two entire LOBs or parts of two LOBs.

CONVERTTOBLOB procedure

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.

CONVERTTOCLOB procedure

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.

COPY procedures

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

CREATETEMPORARY procedures

Creates a temporary LOB in the temporary data partition.

ERASE procedures

Erases all or part of a LOB.

FREETEMPORARY procedures

Frees a temporary LOB in the temporary data partition.

GETCHUNKSIZE functions

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

GETLENGTH functions

Returns the length of the LOB value, in bytes for a BLOB or characters for a CLOB.

GET_STORAGE_LIMIT functions

Returns the storage limit for the LOB type of the specified LOB.

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.

OPEN procedures

Opens a LOB (persistent or temporary) in the indicated mode, read/write or read-only.

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

WRITE procedures

Writes data to the LOB from a specified offset.

WRITEAPPEND procedures

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

dest_lob

Locator for the LOB to which the data is being appended

src_lob

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 and CLOSE 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

VALUE_ERROR

Either the source or destination LOB is NULL.

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

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

lob_loc

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 and CLOSE 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

lob_1

Locator for the first LOB for comparison

lob_2

Locator for the second LOB for comparison

amount

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

offset_1

Offset in bytes or characters in the first LOB (starting from 1)

offset_2

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 if amount, offset_1, or offset_2 is an invalid value, outside the range 1 to BLOBMAXSIZE or CLOBMAXSIZE (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 for COMPARE is specified to be greater than CLOBMAXSIZE/n, then COMPARE matches characters in a range of size that is either CLOBMAXSIZE/n or Max(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

dest_lob

Locator for the destination LOB

src_clob

Locator for the source LOB

amount

Number of characters to convert from the source LOB

If you want to convert the entire CLOB or NCLOB, pass the constant CLOBMAXSIZE. If you pass any other value, it must be less than or equal to the size of the LOB.

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.

(OUT) The new offset in bytes after the end of the write

src_offset

(IN) Offset in characters in the source LOB for the start of the read

(OUT) Offset in characters in the source LOB right after the end of the read

blob_csid

Character set ID for the converted BLOB data

lang_context

(IN) Language context, such as shift status, for the current conversion (ignored by TimesTen)

(OUT) The language context at the time when the current conversion is done (set to 0 by TimesTen)

This parameter is not supported by TimesTen.

warning

Warning message

This parameter is not supported by TimesTen.


Usage notes

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

amount

CLOBMAXSIZE (IN)

Convert the entire LOB.

dest_offset

1 (IN)

Start from the beginning.

src_offset

1 (IN)

Start from the beginning.

blob_csid

DEFAULT_CSID (IN)

Default character set ID, use same ID as source CLOB.

lang_context

DEFAULT_LANG_CTX (IN)

This is the default language context (ignored by TimesTen).

warning

NO_WARNING (OUT)

WARN_INCONVERTIBLE_CHAR (OUT)

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 and src_offset values are in characters and the dest_offset is in bytes. To convert the entire LOB, you can specify CLOBMAXSIZE for the amount parameter.

  • CONVERTTOBLOB gets the source and destination LOBs as necessary prior to conversion and write of the data.

Exceptions

Table 2-11 CONVERTTOBLOB procedure exceptions

Exception Description

VALUE_ERROR

Any of the input parameters is NULL or invalid.

INVALID_ARGVAL

Any of the following is true:

src_offset < 1 or src_offset > CLOBMAXSIZE

dest_offset < 1 or dest_offset > BLOBMAXSIZE

amount < 1 or amount > CLOBMAXSIZE


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

dest_lob

Locator for the destination LOB

src_blob

Locator for the source LOB

amount

Number of bytes to convert from the source LOB

If you want to convert the entire BLOB, pass the constant BLOBMAXSIZE. If you pass any other value, it must be less than or equal to the size of the BLOB.

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.

(OUT) The new offset in characters after the end of the write

This offset always points to the beginning of the first complete character after the end of the write.

src_offset

(IN) Offset in bytes in the source LOB for the start of the read

(OUT) Offset in bytes in the source LOB right after the end of the read

blob_csid

Character set ID for the source BLOB data

lang_context

(IN) Language context, such as shift status, for the current conversion (ignored by TimesTen)

(OUT) Language context at the time when the current conversion is done (set to 0 by TimesTen)

This parameter is not supported by TimesTen.

warning

Warning message

This parameter is not supported by TimesTen.


Usage notes

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

amount

BLOBMAXSIZE (IN)

Convert the entire LOB.

dest_offset

1 (IN)

Start from the beginning.

src_offset

1 (IN)

Start from the beginning.

blob_csid

DEFAULT_CSID (IN)

Default character set ID, use same ID as destination CLOB.

lang_context

DEFAULT_LANG_CTX (IN)

This is the default language context (ignored by TimesTen).

warning

NO_WARNING (OUT)

WARN_INCONVERTIBLE_CHAR (OUT)

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 and src_offset values are in bytes and the dest_offset is in characters. To convert the entire LOB, you can specify BLOBMAXSIZE for the amount parameter.

  • CONVERTTOCLOB gets the source and destination LOBs as necessary prior to conversion and write of the data.

Exceptions

Table 2-14 CONVERTTOCLOB procedure exceptions

Exception Description

VALUE_ERROR

Any of the input parameters is NULL or invalid.

INVALID_ARGVAL

Any of the following is true:

src_offset < 1 or src_offset > BLOBMAXSIZE

dest_offset < 1 or dest_offset > CLOBMAXSIZE

amount < 1 or amount > BLOBMAXSIZE


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

dest_lob

Locator for the destination LOB being copied to

src_lob

Locator for the source LOB being copied from

amount

Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to copy

dest_offset

Offset in bytes or characters in the destination LOB for the start of the copy (starting from 1)

src_offset

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 and CLOSE 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

VALUE_ERROR

Any of the input parameters is NULL or invalid.

INVALID_ARGVAL

Any of the following is true:

src_offset < 1 or src_offset > maximum LOB size

dest_offset < 1 or dest_offset > maximum LOB size

amount < 1 or amount > maximum LOB size

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

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.

Important:

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

lob_loc

Locator for the temporary LOB

It is permissible to specify an NCLOB locator instead of a CLOB locator. The appropriate character set is used.

cache

Flag indicating whether the LOB should be read into buffer cache

dur

One of two predefined duration values—SESSION or CALL—that specifies a hint as to when the temporary LOB is cleaned up

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

lob_loc

Locator for the LOB

amount

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

(OUT) Number of bytes or characters actually erased

offset

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 the amount parameter.

  • It is recommended that you enclose write operations to the LOB with OPEN and CLOSE 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

VALUE_ERROR

Any input parameter is NULL.

INVALID_ARGVAL

Any of the following is true:

amount < 1 or amount > maximum LOB size

offset < 1 or offset > maximum LOB size

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

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 the section on 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

lob_loc

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 and FREETEMPORARY 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

lob_loc

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

BUFFERING_ENABLED

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

lob_loc

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 or WRITE 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

BUFFERING_ENABLED

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

lob_loc

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

lob_loc

Locator for the LOB

pattern

Pattern to be tested for

The pattern is in RAW bytes for BLOBs, or a character string (VARCHAR2) for CLOBs or NCLOBs. The maximum size of the pattern is 16383 bytes.

offset

Absolute offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs), starting from 1, at which the pattern-matching is to start

nth

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 is NULL or invalid or any of the following is true:

    • offset < 1 or offset > maximum LOB size

    • nth < 1 or nth > maximum LOB size

    Where maximum LOB size is BLOBMAXSIZE for a BLOB or CLOBMAXSIZE for a CLOB.

Usage notes

  • For a CLOB or NCLOB, the form of the VARCHAR2 buffer for the pattern parameter must be appropriate for the type of LOB. If the specified LOB is of type NCLOB, the pattern must contain NCHAR data. If the specified LOB is of type CLOB, the pattern must contain CHAR data.

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

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

lob_loc

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

lob_loc

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 to NULL. Consequently, ISTEMPORARY returns 0 (zero) for a locator that has been freed but not explicitly reset to NULL.

  • CREATETEMPORARY cannot be used to create a temporary passthrough LOB; however, if one is created using some other mechanism, such as SQL, ISTEMPORARY and FREETEMPORARY 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

lob_loc

Locator for the LOB

open_mode

Mode in which to open, either LOB_READONLY or LOB_READWRITE


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 the OPEN call.

  • It is not mandatory that you wrap LOB operations inside OPEN and CLOSE 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

lob_loc

Locator for the LOB

amount

(IN) Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to read

(OUT) Number of bytes or characters actually read

offset

Offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) from the start of the LOB (starting from 1)

buffer

Output buffer from the read operation


Usage notes

  • If the input offset points past the end of the LOB, then amount is set to 0 (zero) and a NO_DATA_FOUND exception is raised.

  • For a CLOB or NCLOB, the form of the VARCHAR2 buffer for the buffer parameter must be appropriate for the type of LOB. If the specified LOB is of type NCLOB, the buffer must contain NCHAR data. If the specified LOB is of type CLOB, the buffer must contain CHAR 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

VALUE_ERROR

Any of lob_loc, amount, or offset is NULL.

INVALID_ARGVAL

Any of the following is true:

amount < 1 or amount > 32767 bytes (or the character equivalent) or the capacity of buffer

offset < 1 or offset > maximum LOB size

NO_DATA_FOUND

The end of the LOB is reached and there are no more bytes or characters to read from the LOB. The amount parameter has a value of 0 (zero).


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.

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

lob_loc

Locator for the LOB

amount

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

offset

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 is NULL or any of the following is true:

    • amount < 1 or amount > 32767 bytes (or the character equivalent)

    • offset < 1 or offset > maximum LOB size

    Where maximum LOB size is BLOBMAXSIZE for a BLOB or CLOBMAXSIZE for a CLOB.

Usage notes

  • For fixed-width n-byte CLOBs or NCLOBs, 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 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 type NCLOB, the buffer must contain NCHAR data. If the specified LOB is of type CLOB, the buffer must contain CHAR 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 as a consequence of the character byte size exceeding the available buffer, the user should either call SUBSTR 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.

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

lob_loc

Locator for the LOB

newlen

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 and CLOSE 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

VALUE_ERROR

The lob_loc value is NULL.

INVALID_ARGVAL

Either of the following is true:

newlen < 0 or newlen > maximum LOB size

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

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.

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

lob_loc

Locator for the LOB

amount

Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to write

offset

Offset in bytes (for BLOBs) or characters (for CLOBs or NCLOBs) from the start of the LOB for the write operation (starting from 1)

buffer

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 the buffer parameter must be appropriate for the type of LOB. If the specified LOB is of type NCLOB, the buffer must contain NCHAR data. If the specified LOB is of type CLOB, the buffer must contain CHAR 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 and CLOSE 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

VALUE_ERROR

Any of lob_loc, amount, or offset is NULL, out of range, or invalid.

INVALID_ARGVAL

Any of the following is true:

amount < 1 or amount > 32767 bytes (or the character equivalent) or capacity of buffer

offset < 1 or offset > maximum LOB size

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

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

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

lob_loc

Locator for the LOB

amount

Number of bytes (for BLOBs) or characters (for CLOBs or NCLOBs) to write

buffer

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 the buffer parameter must be appropriate for the type of LOB. If the specified LOB is of type NCLOB, the buffer must contain NCHAR data. If the specified LOB is of type CLOB, the buffer must contain CHAR 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 and CLOSE 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

VALUE_ERROR

Any of lob_loc, amount, or offset is null, out of range, or invalid.

INVALID_ARGVAL

Any of the following is true:

amount < 1 or amount > 32767 bytes (or the character equivalent) or capacity of buffer

QUERY_WRITE

Cannot perform a LOB write inside a query. (This is not applicable for TimesTen.)

BUFFERING_ENABLED

Cannot perform operation if LOB buffering is enabled on the LOB.