Oracle8i Application Developer's Guide - Large Objects (LOBs) Release 2 (8.1.6) Part Number A76940-01 |
|
Internal Persistent LOBs, 15 of 42
See:
"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.
Use of TO_LOB
is subject to the following limitations:
TO_LOB
to copy data to a LOB column, but not to a LOB attribute.
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;
BEFORE
INSERT
or INSTEAD
OF
INSERT
-- the :NEW.lob_col
variable can't be referenced in the trigger body.
TO_LOB
inside any PL/SQL block.
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;
This functionality is based on using an operator on LONG
s 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:
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);
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;
LONG
column.
For example, issue the following SQL command to drop the LONG_TAB
table:
DROP TABLE Long_tab;
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 ALTER
ed the table and ADD
ed 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.
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|