[thesite] problems copying a LONG column in oracle

[Dean Mah]

rudy writes:

> this is directed to the oracle gurus  (adam/tcm614ce?  dan?  anybody?)
> 
> we have a table with a LONG column that we need to migrate to a new table
>
> dan, can you remember why we wanted to go from LONG to CLOB?

According to the new features of Oracle 8.1.5 you should be able to
do:

   INSERT INTO tab_new SELECT TO_LOB(long_value) FROM tab_old;

Got this from:

   http://www.akadia.com/services/ora815nf.html

If this doesn't work, you could write a quick little Perl script that
would copy it over.


Oracle encourages migration from LONG to CLOB for the following
reasons:

1.  CLOB Capacity

      - With Oracle8i, CLOBs can store up to 4GB of data. This doubles
        the 2GB of data that LONG and LONG RAW data types could store
        in earlier releases of Oracle.

2.  Number of CLOB Columns per Table

      - An Oracle8i table can have multiple CLOB columns. Each CLOB
        column in the same table can be of a different type. Tables
        are limited to a single LONG or LONG RAW column.

3.  Random Piece-wise Access

      - CLOBs support random access to data, but LONGs support only
        sequential access. Further, to improve the speed with which a
        CLOB can be brought from the server-side to the client, the
        CLOB can be broken into chunks that can then be brought in a
        single round trip back to the client.


I also know that you can store LOBs in a separate tablespace from your
regular data which will give you a perfomance boost.

I also heard a rumour that they would be removing the LONG datatype in
future releases.  However, like the rumour to remove rule-based
optimization, that hasn't happened yet and it may not for a long time
to come.

If you are insteresting in using LOBs, you may want to check out the
DBMS.LOB stored procedure which will give you more access to them.

Dean