Oracle TO_LOB() Function

Oracle TO_LOB() is a built-in function that converts LONG or LONG RAW values in a given column to LOB values. You can only apply this function to LONG or LONG RAW columns and only in the select list of a subquery in an INSERT statement.

Oracle TO_LOB() Syntax

Here is the syntax of the Oracle TO_LOB() function:

TO_LOB(long_column)

Parameters

long_column

Required. The name of the column to be operated on.

Return Value

The Oracle TO_LOB() function converts LONG or LONG RAW values in the given column to LOB values. The name of the column to be operated on.

Before using this function, you must create a LOB column to receive the converted LONG value. To convert LONG values, create a CLOB column. To convert LONG RAW values, create a BLOB column.

If you are creating an index-organized table, you cannot use the TO_LOB function to convert a LONG column to a LOB column in the subquery of a CREATE TABLE...AS SELECT statement. Instead, create an index-organized table without the LONG column first, and then use the TO_LOB function in an INSERT...AS SELECT statement.

You cannot use this function in a PL/SQL package. Instead, use the TO_CLOB(character) or TO_BLOB(raw) function.

If any parameter is NULL, TO_LOB() returns NULL.

Oracle TO_LOB() Example

The following syntax shows how to use the TO_LOB function to handle your LONG data in the assumed table old_table:

CREATE TABLE new_table (col1, col2, ... lob_col CLOB);
INSERT INTO new_table (select o.col1, o.col2, ... TO_LOB(o.old_long_col)
   FROM old_table o;

Conclusion

Oracle TO_LOB() is a built-in function that converts LONG or LONG RAW values in a given column to LOB values. You can only apply this function to LONG or LONG RAW columns and only in the select list of a subquery in an INSERT statement.