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

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

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l