LOB Data Types

LOB data types are not supported in TimesTen Scaleout.

The large object (LOB) data types can store large and unstructured data such as text, image, video, and spatial data. LOBs include the BLOB, CLOB and NCLOB data types.

You can insert or update data in a column that is of a LOB data type. For update operations, you can set the LOB value to NULL, an empty value through EMPTY_CLOB or EMPTY_BLOB, or replace the entire LOB with new data. You can update a LOB value with another LOB value. If you delete a row containing a LOB column, you also delete the LOB value.

LOB data type semantics are similar to the following SQL semantics:

• BLOB data types use SQL VARBINARY semantics.

• CLOB data types use SQL VARCHAR2 semantics.

• NCLOB data types use SQL NVARCHAR2 semantics.

The following SQL statements, operators, and functions accept one or more of the LOB data types as arguments.

• SQL statements: CREATE TABLE, SELECT, INSERT, and UPDATE

• Operators: LIKE and IS [NOT] NULL

• Functions: ASCIISTR, CONCAT, INSTR, INSTRB, INSTR4, LENGTH, LENGTHB, LOWER, LPAD, NLSSORT, NVL, TRIM, LTRIM, RTRIM, SUBSTR, SUBSTRB, SUBSTR4, REPLACE, RPAD, SOUNDEX, TO_DATE, TO_NUMBER, TO_CHAR, and UPPER

Note:

Support for LOB data types is detailed in documentation for the above statements, operators, and functions. Refer to SQL Statements, Search Conditions, and Functions, respectively.

Description

• LOB conversion SQL functions (TO_BLOB, TO_CLOB, and TO_LOB) convert to the desired LOB data type.

• LOB columns are always stored out of line, so you cannot use the INLINE attribute when declaring LOB columns.

• You can define multiple columns of the LOB data type within a single table.

• You cannot create a primary key on a LOB column. You cannot define an index on a LOB column.

• You cannot create a materialized view if the detail table contains a LOB column.

• In addition to SQL, you can use LOB specific APIs in PL/SQL, ODBC, JDBC, OCI, and PRO*C/C++ for creating and updating LOBs. See the appropriate TimesTen developer's guide for more information on these APIs.

The following sections describe each LOB data type in more detail:

• BLOB

• CLOB

• NCLOB

In addition, the following sections provide more details on LOBs in general:

• Difference Between NULL and Empty LOBs

• Initializing LOBs

BLOB

The Binary LOB (BLOB) data type stores unstructured binary large objects. The maximum size for BLOB data is 16 MB.

Note:

For details on assigning hexadecimal literals as binary data in TimesTen, see the description for the HexadecimalLiteral in "Constants".

When you define a BLOB in a column, you do not define the maximum number of characters as you would with VARBINARY and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE blob_content (id NUMBER PRIMARY KEY, 
                                    blob_column BLOB ); 

To manipulate a BLOB, the following functions are provided:

• There are two methods to initialize a BLOB, including the EMPTY_BLOB function to initialize an empty BLOB. For details on initializing a BLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference Between NULL and Empty LOBs".

• To convert a binary value to a BLOB, use the TO_LOB or TO_BLOB functions. See "TO_BLOB" and "TO_LOB" for more details.

CLOB

The Character LOB (CLOB) data type stores single-byte and multibyte character data. The maximum size for CLOB data is 4 MB. The maximum number of characters that can be stored in the CLOB depends on whether you are using a single or multibyte character set.

When you define a CLOB in a column, you do not define the maximum number of characters as you would with VARCHAR and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE clob_content (id NUMBER PRIMARY KEY, 
                                    clob_column CLOB ); 

To manipulate a CLOB, the following functions are provided:

• There are two methods to initialize a CLOB, including the EMPTY_CLOB function to initialize an empty CLOB. For details on initializing a CLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference Between NULL and Empty LOBs" below.

• To convert a character string to a CLOB, use the TO_LOB or TO_CLOB functions. See "TO_CLOB" and "TO_LOB" for more details.

NCLOB

The National Character LOB (NCLOB) data type stores Unicode data. The maximum size for an NCLOB data is 4 MB.

When you define a NCLOB in a column, you do not define the maximum number of characters as you would with VARCHAR and other variable length data types. Instead, the definition for the column would be as follows:

Command> CREATE TABLE nclob_content (id NUMBER PRIMARY KEY, 
                                     nclob_column NCLOB ); 

The following functions support the NCLOB data type:

• There are two methods to initialize an NCLOB, including the EMPTY_CLOB function to initialize an empty NCLOB. For details on initializing a NCLOB, see "Initializing LOBs". For details on how an empty LOB is different from a NULL LOB, see "Difference Between NULL and Empty LOBs", immediately below.

• To convert a character string to an NCLOB, use the TO_LOB or TO_CLOB functions. See "TO_CLOB" and "TO_LOB" for more details.

Difference Between NULL and Empty LOBs

A NULL LOB has a different meaning than an empty LOB.

• A NULL LOB has the value of NULL, so NULL is returned if you request a NULL LOB.

• An empty LOB is initialized with either the EMPTY_CLOB or EMPTY_BLOB functions. These functions initialize the LOB to be a zero-length, non-NULL value. You can also use the EMPTY_CLOB or EMPTY_BLOB functions to initialize a LOB in a non-nullable column.

Initializing LOBs

You can initialize a LOB in one of two ways:

• You can insert an empty LOB into a BLOB, CLOB or NCLOB column by using the EMPTY_BLOB or EMPTY_CLOB functions. This is useful when you do not have any data, but want to create the LOB in preparation for data. It is also useful for initializing non-nullable LOB columns.

• Initialize the LOB by inserting data directly. There is no need to initialize a LOB using the EMPTY_BLOB or EMPTY_CLOB functions, you can simply insert the data directly.

The following demonstrates examples of each type of initialization:

You can initialize a LOB with the EMPTY_CLOB function, as shown with the following example:

Command> INSERT INTO clob_content (id, clob_column)
         VALUES (1, EMPTY_CLOB( ) );
1 row inserted.

You can initialize a LOB by inserting data directly, as shown with the following example:

Command> INSERT INTO clob_content(id, clob_column)
         VALUES (4, 'Demonstration of the LOB initialization.');
1 row inserted.

You can initialize or update an existing LOB value with the UPDATE statement, as shown with the following examples:

Command> UPDATE blob_content
         SET blob_column = 0x000AF4511
         WHERE id = 1;
1 row updated.

Command> SELECT * FROM blob_content;
 < 1, 0000AF4511 >
1 rows found.

Command> UPDATE clob_content
         SET clob_column = 'Demonstration of the CLOB data type '
         WHERE id = 1;
1 row updated.

Command> SELECT * FROM clob_content;
 < 1, Demonstration of the CLOB data type >