Sunday, June 1, 2008

How to Convert Long data type to Lob

It is relatively easy if you want to convert long datatype to lob data type.
This section describes the following techniques for migrating existing tables from LONG to LOB datatypes:

A)Using ALTER TABLE to Convert LONG Columns to LOB Columns

B)Copying a LONG to a LOB Column Using the TO_LOB Operator

C)Online Redefinition of Tables with LONG Columns where high availability is critical

D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility

With an example I have demonstrate these procedures.
A)Using ALTER TABLE to Convert LONG Columns to LOB Columns
----------------------------------------------------------------------


SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');

1 row created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (2,'This is the second entered row');

1 row created.

SQL> COMMIT;
Commit complete.

SQL> ALTER TABLE TEST_LONG_LOB MODIFY B CLOB;
Table altered.

SQL> DESC TEST_LONG_LOB;

Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB


B)Copying a LONG to a LOB Column Using the TO_LOB Operator
---------------------------------------------------------------------
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);

Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');

1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TABLE TEST_CLOB AS SELECT A, TO_LOB(B) B FROM TEST_LONG_LOB;

Table created.

After you ensure that the data is accurately copied, you can drop the original table and create a view or synonym for the new table using one of the following sequences:

SQL> DROP TABLE TEST_LONG_LOB;

Table dropped.

SQL> CREATE VIEW TEST_LONG_LOB AS SELECT * FROM TEST_CLOB;
View created.

or

SQL> DROP TABLE TEST_LONG_LOB;

Table dropped.

SQL> CREATE SYNONYM TEST_LONG_LOB FOR TEST_CLOB;

Synonym created.

or rename the table
SQL> RENAME TEST_CLOB TO TEST_LONG_LOB;

Table renamed.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B CLOB

C)Online Redefinition of Tables with LONG Columns where high availability is critical
---------------------------------------------------------------------------------------------

1)This is the table that need to change LONG data.
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> COMMIT;
Commit complete.

2) Determine if the table is a candidate for online re-organization
SQL> exec dbms_redefinition.can_redef_table('ARJU','TEST_LONG_LOB');
PL/SQL procedure successfully completed.
A primary key is mandatory since materialized views and logs are created during the start of redefinition.

3)Create an Interim Table.

SQL> CREATE TABLE TEST_LONG_LOB_INT(A NUMBER NOT NULL, B CLOB);
Table created.

Note that interim table has no primary key.

4)Start the re-organization process
SQL>declare
col_mapping varchar2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'a a , '||
'to_lob(b) b';

dbms_redefinition.start_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT', col_mapping);
END;
/

PL/SQL procedure successfully completed.
Here Arju is username.

5)Run dbms_redefinition.copy_table_dependents
SQL>declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT',
1, true, true, true, false,
error_count);

dbms_output.put_line('errors := ' || to_char(error_count));
END;
/

PL/SQL procedure successfully completed.
6)Execute dbms_redefinition.finish_redef_table procedure.

exec dbms_redefinition.finish_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT');
PL/SQL procedure successfully completed.

SQL> DROP TABLE TEST_LONG_LOB_INT;

Table dropped.


SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB



D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility
------------------------------------------------------------------------------------------

If you are exporting data as part of a migration to a new database, create a table on the destination database with LOB columns and Data Pump will call the LONG-to-LOB function implicitly.
Serach within my blog about data pump export or import.
Related Documents

2 comments:

Mike said...

Thank for the different ways you shown. It helped me a lot.

Anonymous said...

I have a table which contains long field, this long fields contains XML data. I did an export of this table using oracle datapump and I truncated this table, Later I modified the table to convert the long column to clob and I imported data in to this table using oracle data pump, but the data in clob field is corrupted. The data pump conveted long data to clob , but it inserted lot of junk characters in to the field. I am using oracle version 10.2.0.4, what is the reason that data pump imported with junk characters in between.