Re: Getting long value

Eitan M wrote:
> What if I use LONG in DB, and it is almost imposible to change
> (standard program that create LONGS, and not LOBS, and lot of customers).
> Isn't any function to convert LONG to LOB (or to VARCHAR2 if I know it has
> only characters) , so I can use it normally.
>
> Besides, I didn't understand what DBMS_CRYPTO is related to my question ?
>
> Thanks :)
>
> "Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> wrote in message
> news:1162804916.721934.169330_at_k70g2000cwa.googlegroups.com...
> >
> > Eitan M wrote:
> >> Hello,
> >> I want to compare two LONG columns in a specific table (Oracle 8i).
> >> Maybe by the CRC code for the LONG column.
> >>
> >> Is there any function that convert the LONG column to CRC string ?
> >> Also I want to know which byte makes the difference.
> >> Is there any other solution for the above ?
> >>
> >> Thanks :)
> >
> > The easiest solution is to take Oracle recommended approach: convert
> > all LONGs to LOBs in your db and never use LONGs again (see the docs
> > about the transition, it's covered in big detail there.) LOBs are much
> > easier to work with, there's DBMS_LOB.COMPARE function that can compare
> > two LOBs or equally sized parts thereof, and there's DBMS_CRYPTO.HASH
> > in 10g, which can MD5 or SHA-1 a LOB. Getting positions where the
> > difference starts and ends is not possible using OOTB functionality
> > afaik, but you can try to use a BDE implementation in Java for this if
> > you can find one.
> >
> > Hth,
> > Vladimir M. Zakharychev
> > N-Networks, makers of Dynamic PSP(tm)
> > http://www.dynamicpsp.com
> >

You wanted to get a "CRC", that is, checksum of your data. DBMS_CRYPTO.HASH can "checksum" a LOB using MD5 or SHA-1 algorithm. Simply put, you can easily detect if two LOBs are different in content by comparing their hashes.

As DA Morgan posted, there's rather simple way to convert a LONG to a LOB online using a global temporary table and TO_LOB() function. Of course, you will waste some CPU and some I/O on conversion, but if you absolutely can't transition to LOBs, using this approach is about the only way (at least, I am not aware of any other.)

Hth,

     Vladimir M. Zakharychev
     N-Networks, makers of Dynamic PSP(tm)
     http://www.dynamicpsp.com