Re: Function-based index on LOBs?

From: Frank Foss <fozzie_beer_at_hotmail.com>
Date: Thu, 23 Dec 2004 08:23:08 -0800
Message-ID: <3309ndF3paqonU1@individual.net>

fitzjarrell_at_cox.net wrote:
> Frank Foss wrote:
>

>>Oracle 9.2.0.4 on Win2000 Advanced Server
>>
>>I tried to find the info on Tahiti, but came up short on what
>>Oracle-supplied functions can be used in function-based indexes.
>>
>>Here's the idea:
>>We're currently using an in-house application that stores files on

>
> the
>

>>filesystem, and generating SHA1 hashes per file. This checksum is

>
> used
>

>>to check for duplicates.
>>We're looking at inserting the files in the database instead, as

>
> BLOBs.
>

>>To speed up the search for duplicate/identical files, the SHA1 (or

>
> MD5,
>

>>or RMD160 or other message digests) will be stored in the same record

>
>

>>with the file.
>>
>>Is it possible to create a function-based index that will create the
>>message digest (SHA1/MD5) upon insert of the bytestream?
>>
>>Does Oracle provide such a digest function? Is it possible to call an

>
>

>>external function? ( "openssl dgst -md5 <something>" )
>>Or just bite the dust and compute the value and insert it ourselves?
>>
>>Any hints/tips/tricks/pointers to The Fine Manual appreciated
>>Foz

>
>
> The only place the message digest will appear using a function-based
> index is in the index itself, presuming you can create such a
> function-based index. A trigger, on the other hand, can generate your
> digest value upon insert and populate a column in the same record with
> the calculated value. A plain-vanilla index on that column should then
> suffice.
>
> I would seriously consider creating such a trigger; you have fewer
> restrictions on a trigger, and many more functions you can use to
> create your digest.

Thank you very much for your input.
It is probably a better concept.

The question is then turning into:
Is there a message digest function available in PL/SQL that can compute SHA1 or MD5 values? Again, I could not find references to an existing Oracle function.

>
> My two cents.
>
>
> David Fitzjarrell
>

Much appreciated
Foz Received on Thu Dec 23 2004 - 10:23:08 CST