||Oracle Tips by Burleson
Oracle 10g Unloading LONG and LONG RAW Datatypes
LONG and LONG RAW columns can be unloaded by the
oracle_datapump access driver, however, that data can only be loaded
back into the database as LOB fields. The following example
demonstrates the unloading of LONG and LONG RAW datatypes.
For all LONG or LONG RAW columns in the table
being unloaded, define the corresponding columns in the external
table as CLOB for LONG columns or BLOB for LONG RAW columns.
CREATE TABLE proj_tab
SQL> INSERT INTO proj_tab VALUES (1,
'Project to write an New Features book');
1 row created.
To unload this data using the datapump, you
must create an external table that contains a CLOB column to contain
the data from the LONG column. When loading the external table, the
to_lob operator must be used to convert the LONG column into a CLOB.
CREATE TABLE proj_tab_ext
SELECT proj_no, TO_LOB(description) description FROM proj_tab;
The unloaded data in the external table can now
be used to create another table similar to the one that was
unloaded, except the new table will contain a CLOB column instead of
CREATE TABLE proj_lob_tab
SELECT * from proj_tab_ext;
After an operation of this sort you
should always verify that the table was properly created.
SELECT * FROM proj_lob_tab;
Project to write an New Features book
Get the complete story:
To get the code instantly, click here:
Need an Oracle Mentor?
BEI is now offering personal mentors for Oracle DBAs where you can have an
Oracle expert right at your fingertips, anytime day or night. We work with
hundreds of Oracle databases every year, so we know exactly how to quickly
assist you with any Oracle question.
Why risk an unplanned outage? You can now get telephone access to Don
Burleson or any of his Oracle Certified DBAs with more than 20 years of
full-time IT experience. Click here for details: