A client of mine recently had the need to generate SHA 256-bit checksums on BLOB columns stored in an Oracle 11g database. My first reaction was to say "No Problem, we'll just use either the standard_hash function or the dbms_crypto.hash function".
I soon discovered that Oracle 11g doesn't have SHA 256-bit capabilities. I then embarked on an extensive Google search and had a few false starts - mostly finding solutions for VARCHAR2 fields but not for BLOBs.
However, after a while I came across this stackoverflow entry from August 2013.
https://stackoverflow.com/ questions/18522525/compute- blob-hash-in-trigger-on-blobs- table
So I ran the code below to create the java stored procedure and two wrapper functions, tested with a couple of BLOB columns containing files, compared the result with the checksum computed for the same files on the file system and everything worked perfectly!
Problem solved, client happy!
Here's the code, and a shout-out to the original developers of this solution: Sean Stuber (https://twitter.com/sdstuber) and AdiM. Thanks guys!
I soon discovered that Oracle 11g doesn't have SHA 256-bit capabilities. I then embarked on an extensive Google search and had a few false starts - mostly finding solutions for VARCHAR2 fields but not for BLOBs.
However, after a while I came across this stackoverflow entry from August 2013.
https://stackoverflow.com/
So I ran the code below to create the java stored procedure and two wrapper functions, tested with a couple of BLOB columns containing files, compared the result with the checksum computed for the same files on the file system and everything worked perfectly!
Problem solved, client happy!
Here's the code, and a shout-out to the original developers of this solution: Sean Stuber (https://twitter.com/sdstuber) and AdiM. Thanks guys!
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sha2 AS
import java.security.MessageDigest;
import oracle.sql.*;
public class sha2
{
public static oracle.sql.RAW get_digest_string( String p_string, int p_bits ) throws Exception
{
MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
byte[] v_digest;
v_digest = v_md.digest( p_string.getBytes( "UTF-8" ) );
return RAW.newRAW(v_digest);
}
public static oracle.sql.RAW get_digest_blob( oracle.sql.BLOB p_blob, int p_bits ) throws Exception
{
byte[] allBytesInBlob;
allBytesInBlob = p_blob.getBytes(1, (int) p_blob.length());
MessageDigest v_md = MessageDigest.getInstance( "SHA-" + p_bits );
byte[] v_digest = v_md.digest( allBytesInBlob );
return RAW.newRAW(v_digest);
}
}
/
CREATE OR REPLACE FUNCTION sha2_string(p_string in VARCHAR2, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_string( java.lang.String, int ) return oracle.sql.RAW';
/
CREATE OR REPLACE FUNCTION sha2_blob(p_byte in BLOB, p_bits in number)
RETURN RAW AS LANGUAGE JAVA
NAME 'sha2.get_digest_blob( oracle.sql.BLOB, int ) return oracle.sql.RAW';
/
SELECT sha2_string('0123456789',256) FROM DUAL;
SELECT sha2_blob(utl_raw.cast_to_raw('0123456789'),256) FROM DUAL;