Getting long value

#1

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

#2

Eitan M wrote:

Quote:

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

#3

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 (DOT) com> wrote

Quote:

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

#4

Eitan M wrote:

Quote:

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 (DOT) com> wrote in message
news:1162804916.721934.169330 (AT) k70g2000cwa (DOT) 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

Please do not top post.

There is a demo for converting LONG to CLOB in Morgan's Library at
www.psoug.org.

Convert now if you can. Oracle has deprecated the LONG data type.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#5

Eitan M wrote:

Quote:

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 (DOT) com> wrote in message
news:1162804916.721934.169330 (AT) k70g2000cwa (DOT) 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

#6

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Eitan M schrieb:

Quote:

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

You may try a kind of Long to Varchar2-conversion.

This only works up to 4000 signs.
(It helped me selecting the USER_VIEWS.TEXT quite often;-)

Please have a look at:
http://asktom.oracle.com/pls/ask/f?p...A:839298816582

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (Cygwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFUlxDS/wzzi1yrp0RApbAAKDRcdAZCPbg1ZqSRkVqa7621RC6OACgsTKJ
O/BMzTBqxHlCJKio5R9otVo=
=p12R
-----END PGP SIGNATURE-----

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Getting long value

comp.databases.oracle.misc comp.databases.oracle.misc

Getting long value - 11-06-2006 , 02:36 AM

Re: Getting long value - 11-06-2006 , 03:21 AM

Re: Getting long value - 11-06-2006 , 04:08 AM

Re: Getting long value - 11-06-2006 , 12:02 PM

Re: Getting long value - 11-06-2006 , 01:41 PM

Re: Getting long value - 11-08-2006 , 04:37 PM