DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

Trying to convert a LONG or a LONG RAW column to a CLOB or BLOB column respectively in Oracle8i was a rather difficult process. Prior to Oracle9i, you had to first create a second table with the proper LOB column in place of the LONG column in the original table. Let's assume the following DDL that represents the original table:

CREATE TABLE original_emp ( emp_id NUMBER , emp_name VARCHAR2(100) , emp_history LONG);

The following DDL statement creates the second corresponding table with the LOB column:

CREATE TABLE new_emp ( emp_id NUMBER , emp_name VARCHAR2(100) , emp_history CLOB);

You would then need to populate the second table with data from the original table. In the process of populating the second table, you must convert the LONG or LONG RAW data in the original into the CLOB or BLOB data in the second table. To perform this task, Oracle8i introduced the TO_LOB function that enables you to convert a LONG or a LONG RAW value to a CLOB or BLOB value respectively. The following SQL statement can be used to perform this task:

INSERT INTO new_emp ( SELECT emp_id, emp_name, TO_LOB(emp_history) FROM original_emp);

The next step was to DROP the original table (original_emp), and finally you must rename the newly create table (new_emp) to the original table name (original_emp).

Oracle9i greatly simplifies this process of converting a LONG or LONG RAW column into a CLOB or BLOB column respectively. You use the ALTER TABLE ... MODIFY statement to change a LONG or LONG RAW column into a CLOB or BLOB column respectively. Here is the only statement required to perform the column conversion:

ALTER TABLE original_emp MODIFY (emp_history CLOB);

The ALTER TABLE ... MODIFY statement can only convert a LONG or LONG RAW to a CLOB or BLOB column respectively. It will not change a VARCHAR2 or a RAW column into a LOB column.

You must, although, rebuild indexes, and you must drop domain indexes before starting the migration process. You also have to create the index manually on the LOB column after the migration is complete. This conversion requires twice the amount of space because both the LONG and the LOB columns must exist for the duration of the conversion.

For the most part, SQL functions and operators that can accept VARCHAR2 values can also accept CLOB values. All PL/SQL package functions that can accept LONG values can also handle CLOB values. Both SQL and PL/SQL functions that accept RAW values can accept BLOB values too. The PL/SQL engine performs implicit conversions from LOB to VARCHAR and RAW, and the reverse conversions also.

When changing LONG columns to LOB columns, you must keep in mind that LONG columns are allowed in clustered tables, but you cannot have LOB columns in clustered tables.

If you have replicated copies of the original table with the LONG column, you must manually change each replicated table to contain the LOB column.

Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 13-Mar-2012 17:35:13 EDT
Page Count: 12984