Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Conversion from LONG -> CLOB -> BLOB for 1 TB Table?

RE: Conversion from LONG -> CLOB -> BLOB for 1 TB Table?

From: VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com>
Date: Thu, 18 Jan 2007 02:50:36 +0530
Message-ID: <BBD944BCAC3AB4499DFBAFB1D8AF3020026FF4D1@BLRKECMSG11.ad.infosys.com>

Folks

For a 1 TB Table, Can Conversion be Done ONLINE from LONG to BLOB using DBMS_REDEFINITION (shown below):-

NOTE - Respective LONG field Contains Customer's Signature, Photo related JPEGs & thus these JPEGs do "NOT" undergo any Updates after INSERT. Respective Proc*C Banking Application will need to use Different Access Functions to Access the JPEG image after Conversion/Migration to BLOB Datatype is Complete.

Will Conversion from LONG to an intermediate CLOB result in Corruption of the Existing JPEGs Binary Data?

Can dbms_LOB.CONVERTTOBLOB be used (shown below) as this needs the Source Data to be of CLOB Datatype ?

Any Other Advisable Ways to Convert with Minimal Down Time?

DBMS_REDEFINITION.START_REDEF_TABLE ('schema_name',

    'original_table',

    'interim_table',

    'TO_LOB(long_col_name) lob_col_name',

    'options_flag',

    'orderby_cols');

SQL> desc DBMS_LOB

PROCEDURE CONVERTTOBLOB

 Argument Name                  Type                    In/Out Default?

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

 DEST_LOB                       BLOB                    IN/OUT

 SRC_CLOB                       CLOB                    IN

 AMOUNT                         NUMBER(38)              IN

 DEST_OFFSET                    NUMBER(38)              IN/OUT

 SRC_OFFSET                     NUMBER(38)              IN/OUT

 BLOB_CSID                      NUMBER                  IN

 LANG_CONTEXT                   NUMBER(38)              IN/OUT

 WARNING                        NUMBER(38)              OUT



Thanks indeed

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto Sent: Wednesday, January 17, 2007 5:22 PM

> What is the ideal path to convert this LONG field to BLOB Datatype in

> Oracle ver 9.2 & 10gR2?

you should be able to use PL/SQL directly from LONG to BLOB

in both releases. DBMS_LOB is your friend, plus the extended

LOB functionality of some of the native SQL functions themselves.

. . .

> Is Conversion from LONG to the intermediate CLOB & CLOB onwards to
BLOB
> necessarily needed?

I don't think you want to go LONG->CLOB->BLOB at all:

CLOB is for characters and I believe you have jpgs?

If so, go directly LONG->BLOB: much faster and you won't

lose data.

. . .

. . .

. . .

HTH Cheers

Nuno Souto

Received on Wed Jan 17 2007 - 15:20:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US