TO_LOB

The TO_LOB function converts supplied TT_VARCHAR and VARCHAR2 data types to a CLOB and VARBINARY data types to a BLOB.

This function is not supported in TimesTen Scaleout.

SQL syntax

TO_LOB ( ValidDataType )

Parameters

TO_LOB has the parameters:

Parameter	Description
ValidDataType	A value that is of TT_VARCHAR, VARCHAR2, or BINARY data types.

Description

You can use this function only on a TT_VARCHAR, VARCHAR2, or VARBINARY column, and only with the CREATE TABLE AS SELECT or INSERT...SELECT statements on tables with a defined LOB column.

Examples

The following example shows how to use the TO_LOB function within the INSERT...SELECT statement on a table with a LOB column.

Command> CREATE TABLE clb(c CLOB);
Command> CREATE TABLE vc (v VARCHAR2(2000));
Command> INSERT INTO vc(v) 
         VALUES ('Showing the functionality of the TO_LOB function');
1 row inserted.

Command> INSERT INTO clb 
         SELECT TO_LOB(v) FROM vc; 
1 row inserted.

Command> SELECT * FROM clb;
< Showing the functionality of the TO_LOB function >
1 row found.

Because of the restriction mentioned above, you cannot use the TO_LOB function in all cases where you can use the TO_CLOB or TO_BLOB functions. The following example demonstrates the error you receive when you try to use the TO_LOB function in this manner:

Command> SELECT TO_LOB(col1) FROM bvar;
 2610: Operand data type 'BINARY' invalid for operator 
 'TO_LOB' in expr ( TO_LOB( BVAR.COL1 ))
The command failed.