Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF




Text description of functions184b.gif follows
Text description of to_LOB


TO_LOB converts LONG or LONG RAW values in the column long_column to LOB values. You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement.

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


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 if you are creating an index-organized table. Instead, create the index-organized table without the LONG column, and then use the TO_LOB function in an INSERT ... AS SELECT statement.

See Also:
  • the modify_col_properties clause of ALTER TABLE for an alternative method of converting LONG columns to LOB
  • INSERT for information on the subquery of an INSERT statement


The sample table pm.print_media has a column press_release of type LONG. This example re-creates part of the table, with LOB data in the press_release column:

CREATE TABLE new_print_media (
   product_id       NUMBER(6),
   ad_id            NUMBER(6),
   press_release    CLOB);

INSERT INTO new_print_media
   (SELECT p.product_id, p.ad_id, TO_LOB(p.press_release)
      FROM print_media p);