LONG to LOB Copying, Using the TO_LOB Operator

Figure 10-20 Use Case Diagram: Copying LONGs to LOBs Using TO_LOB Operator

Text description of the illustration adl10p23.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

Purpose

This procedure describes how to copy a LONG to a LOB using the TO_LOB operator.

Usage Notes

Use of TO_LOB is subject to the following limitations:

• You can use TO_LOB to copy data to a LOB column, but not to a LOB attribute.

• You cannot use TO_LOB with any remote table. Consequently, all the following statements will fail:

  INSERT INTO tb1@dblink (lob_col) SELECT TO_LOB(long_col) FROM tb2; 
  INSERT INTO tb1 (lob_col) SELECT TO_LOB(long_col) FROM tb2@dblink; 
  CREATE table tb1 AS SELECT TO_LOB(long_col) FROM tb2@dblink; 
   
  

• If the target table (the table with the lob column) has a trigger -- such as BEFORE INSERT or INSTEAD OF INSERT -- the :NEW.lob_col variable can't be referenced in the trigger body.

• You cannot deploy TO_LOB inside any PL/SQL block.

• The TO_LOB function can be used to copy data to a CLOB but not a NCLOB. This is because LONG datatypes have the database CHAR character set and can only be converted to a CLOB which also uses the database CHAR character set. NCLOB on the other hand, use the database NCHAR character set.

Syntax

Use the following syntax reference:

• SQL: Oracle9i SQL Reference , Chapter 4, "Functions" -- TO_LOB.

Scenario

Assume that the following archival source table SoundsLib_tab was defined and contains data:

CREATE TABLE SoundsLib_tab 
(
   Id             NUMBER, 
   Description    VARCHAR2(30), 
   SoundEffects   LONG RAW
);

The example assumes that you want to copy the data from the LONG RAW column (SoundEffects) into the BLOB column (Sound) of the multimedia table, and uses the SQL function TO_LOB to accomplish this.

Examples

The example is provided in SQL and applies to all programmatic environments:

• "SQL: Copying LONGs to LOBs Using TO_LOB Operator"

SQL: Copying LONGs to LOBs Using TO_LOB Operator

 INSERT INTO Multimedia_tab (clip_id,sound) SELECT id, TO_LOB(SoundEffects)
   FROM SoundsLib_tab WHERE id =1;

Note: in order for the above to succeed, execute: CREATE TABLE SoundsLib_tab ( id NUMBER, SoundEffects LONG RAW);

This functionality is based on using an operator on LONGs called TO_LOB that converts the LONG to a LOB. The TO_LOB operator copies the data in all the rows of the LONG column to the corresponding LOB column, and then lets you apply the LOB functionality to what was previously LONG data. Note that the type of data that is stored in the LONG column must match the type of data stored in the LOB. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.

Once you have completed this one-time only operation and are satisfied that the data has been copied correctly, you could then drop the LONG column. However, this will not reclaim all the storage originally required to store LONGs in the table. In order to avoid unnecessary, excessive storage, you are better advised to copy the LONG data to a LOB in a new or different table. Once you have made sure that the data has been accurately copied, you should then drop the original table.

One simple way to effect this transposing of LONGs to LOBs is to use the CREATE TABLE... SELECT statement, using the TO_LOB operator on the LONG column as part of the SELECT statement. You can also use INSERT... SELECT.

In the examples in the following procedure, the LONG column named LONG_COL in table LONG_TAB is copied to a LOB column named LOB_COL in table LOB_TAB. These tables include an ID column that contains identification numbers for each row in the table.

Complete the following steps to copy data from a LONG column to a LOB column:

1. Create a new table with the same definition as the table that contains the LONG column, but use a LOB datatype in place of the LONG datatype.

   For example, if you have a table with the following definition:

   CREATE TABLE Long_tab (
      id       NUMBER,
      long_col LONG);
   

   Create a new table using the following SQL statement:

   CREATE TABLE Lob_tab (
      id       NUMBER,
      blob_col BLOB);
   

   Note: When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB operator only copies data; it does not preserve the table's schema.

2. Issue an INSERT command using the TO_LOB operator to insert the data from the table with the LONG datatype into the table with the LOB datatype.

   For example, issue the following SQL statement:

   INSERT INTO Lob_tab
   

   SELECT id,
   TO_LOB(long_col)
   FROM long_tab;
   

3. When you are certain that the copy was successful, drop the table with the LONG column.

   For example, issue the following SQL command to drop the LONG_TAB table:

   DROP TABLE Long_tab;
   
   

4. Create a synonym for the new table using the name of the table with LONG data. The synonym ensures that your database and applications continue to function properly.

   For example, issue the following SQL statement:

   CREATE SYNONYM Long_tab FOR Lob_tab;
   
   

Once the copy is complete, any applications that use the table must be modified to use the LOB data.

You can use the TO_LOB operator to copy the data from the LONG to the LOB in statements that employ CREATE TABLE...AS SELECT or INSERT...SELECT. In the latter case, you must have already ALTERed the table and ADDed the LOB column prior to the UPDATE. If the UPDATE returns an error (because of lack of undo space), you can incrementally migrate LONG data to the LOB using the WHERE clause. The WHERE clause cannot contain functions on the LOB but can test the LOB's nullness.