fitzjarrell_at_cox.net wrote:
> Frank Foss wrote:
>
>>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
>
>
> Use the DBMS_OBFUSCATION_TOOLKIT.MD5 function. It requires an input
> and an output parmeter. the output being the checksum. I believe it
> will work with your data. but test it before you go off into
> production.
>
That function appears not to exist in the documentation for the
DBMS_OBFUSCATION_TOOLKIT for 9i r2
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_obtool.htm
Received on Wed Dec 29 2004 - 10:37:27 CST
