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

E21645-03
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
PDF · Mobi · ePub

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.


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

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. Also see the first chapter of Oracle Database SecureFiles and Large Objects Developer's Guide.

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:


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.


Note:

Additional information for DBMS_LOB procedures and functions may be found in Oracle Database SecureFiles and Large Objects Developer's Guide. Documentation there is specific to features and functionality supported by Oracle Database.

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

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

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:

Usage notes


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.

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

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.

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

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

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.


Note:

See Oracle Database PL/SQL Language Reference for more information about NOCOPY and passing temporary LOBs as parameters.

Usage notes


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

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


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

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:

Usage notes


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


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


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


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

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:

Usage notes


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

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

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

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.