Oracle8i Application Developer's Guide - Large Objects (LOBs)
Release 2 (8.1.6)

Part Number A76940-01





Go to previous page Go to beginning of chapter Go to next page

Internal Persistent LOBs, 15 of 42

Copy LONG to LOB

Figure 9-18 Use Case Diagram: Copy LONG to LOB


"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs. 


This procedure describes how to copy a LONG to a LOB.

Usage Notes

Use of TO_LOB is subject to the following limitations:


Use the following syntax reference:


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.


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


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


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);


    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,
    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.

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.