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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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***

Other related posts: