Re: Convert Long to Long RAW in 10gR2

Vivek,

Some rather random thoughts:

• I agree with you that converting the LONG to BLOB, although by far the best solution, will impact your program. But I am unclear about by how much. Remember that proc is a pre-processor. If your program were written with OCI functions (particularly the now undocumented but still supported pre-Oracle 8 calls) it would be a resounding "you'll have to rewrite everything" since there is no OCI7 routine to deal with LOBs. Here, some of the conversion work may be performed by proc. If your Pro*C code is relatively basic (such as what you could do in PL/SQL, fetch the LONG into a 32K RAW) there is a chance it will be almost transparent. It may be a very different matter if you are using "dynamic" Pro*C, because with LONGs you must first fetch into a tiny variable to find out (in the indicator variable) how much you must allocate, and I fear it may be different with LOBs (I have used OCI but not Pro*C with LOBs, so I'm somewhat hazy here).
• Conversion from LONG to LONG RAW may not be a piece of cake. Adding a new column and dropping the old one cannot work, because tables can have only one LONG column. Forget about CTAS. And for conversion proper, since you cannot directly cast a LONG to a LONG RAW, your only hope is a program (Pro*C or PL/SQL, assuming that none of your images takes more than 32K) that proceeds line by line (and possibly deletes the migrated row to save space, if it doesn't forget to shrink the source table from time to time - it will take ages) or, what might be better, downloading and reloading with SQL*Loader.

All considered, I think that you would be better off following Oracle's advice. At least you have routines to convert LONGs to BLOBs. IMHO developers have time enough to modify their code while the migration runs.

Interesting project.

HTH Stéphane Faroult

VIVEK_SHARMA wrote:
>
> Folks
>
> Of a 10gR2 Database, in a 700 GB Table the LONG Field contains
> standard JPEG Image with JFIF Compression.
>
> Running CSSCAN to check Character Set conversion from
> US7ASCII(default) to AL32UTF8 shows all rows as “Lossy conversion”
>
> Oracle Corp’s reply “If you wan to store JPEG’s in a Table then you
> need to use LONG RAW instead of LONG or better BLOB & you need to
> change this *before* going to UTF8.”
>
> NOTE – A Pro*C Application accesses the respective LONG field to
> display the Respective JPEG Image.
>
> Qs. What are easier ways to convert the LONG field to a LONG RAW
> field? Will conversion need an additional 700 GB Space?
>
> Qs Will conversion to LONG RAW field impact the Image access
> Functions, respective Pro*C Code & thus need a corresponding Change?
> (Conversion to BLOB will certainly necessitate a Change in the manner
> of access i.e. respective Functions & Pro*C Code, to my understanding)
>
> Thanks indeed
>

--
http://www.freelists.org/webpage/oracle-l