LONG to CLOB

Yes, I know that LONG datatype was considered "dead" already for Oracle 8i. But still I have cases when people are asking about the conversion to CLOB. Finally, I decided to build a "memo" on this topic.

Small remark: in this blog post I explicitly ignore 11g SecureFile LOB implementation, since the chances of people still using LONG datatypes to step up to the most recent feature are not really high.
--------------------

From the database point of view it is very simple to change LONG to CLOB datatype, since ALTER TABLE..MODIFY supports such change explicitly. But since CLOB columns have many more parameters to be configured, the process of conversion provides a good opportunity to make appropriate setups.

The most critical
*Tablespace - CLOBs could use a different tablespace comparing to the table itself, which could be useful for the purposes of data storage management.
* Retention - configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the tablespace (PCTVERSION, which is used by default for columns converted from LONG). The RETENTION parameter is designed for be used with Undo features of the database, such as Flashback Versions Query, and recommended for all DB configurations using Automatic Undo Management.
* Caching – defines whether DML operations against LOB columns would use BUFFER CACHE or Direct IO operations. There are 3 possible settings of this parameter – CACHE / CACHE READS / NOCACHE (default). Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.

Overall, the migration will take a form of running the following statements to each LONG column (as you can see, column names is not changed – only its datatype and extra storage options)

alter table <TableName> modify <ColumnName> CLOB
lob(<ColumnName>) store as 
(cache 
 tablespace <TablespaceName>
 retention
 )

From the application point of view the following issues/remarks are detected:
* There are no known areas where LONG datatype is doing anything better  (performance/functionality) than CLOB
* There is no easy technique to modify CLOB column back to LONG without using some kind of staging solution.
* PL/SQL supports explicit casting of CLOB columns to LONG variables as long as the length of retrieved data meets LONG restrictions. The next example should read/change/write:

declare
    v_long long;
begin
    select clob_column
    into v_long
    from t_table;
    
    v_long:= v_long||'ABCDEFG';
    
    update t_table
    set clob_column = v_long
    where pk_id = 1;
end;

It is recommended to do a full code review and remove variables of type LONG, especially in performance-critical modules (because DBMS_LOB APIs are much more efficient than, for example, concatenation)

--------------------

Hope, it helps!