2 DBMS_LOB

Using DBMS_LOB

• Overview

• Security model

• Constants

• Data types

• Rules and limits

• Operational notes

• Exceptions

---

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:

---

Data types

The DBMS_LOB package uses the data types shown in Table 2-2.

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

• Maximum buffer size

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

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

• Temporary LOBs

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

Summary of DBMS_LOB subprograms

---

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

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

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

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

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

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

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.

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

---

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

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.

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

---

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

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.

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.

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

Usage notes

• CREATETEMPORARY cannot be used to create a temporary passthrough LOB.

---

ERASE procedures

This procedure erases all or part of a LOB.

Syntax

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   BLOB,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

DBMS_LOB.ERASE (
   lob_loc           IN OUT   NOCOPY   CLOB CHARACTER SET ANY_CS,
   amount            IN OUT   NOCOPY   INTEGER,
   offset            IN                INTEGER := 1);

Parameters

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.

Exceptions

Maximum LOB size is BLOBMAXSIZE for a BLOB or CLOBMAXSIZE for a CLOB.

---

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

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

Return values

Returns the value 32K, but applications should not rely on this number for performance tuning.

Exceptions

---

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

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

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

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.

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

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

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

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

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

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.

---

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

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

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.

---

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

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.

---

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

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