oracle blob md5 函数,如何取BLOB类型的MD5加密值

Here is the my_encryption package code ...........

CREATE OR REPLACE PACKAGE BODY my_encryption IS

/*

|| Local variable to hold the current encryption key.

*/

ps_encryption_key RAW(32);

/*

|| Local exception to hide Oracle -28231 Error Code.

*/

INTERNAL_BAD_KEY exception;

PRAGMA EXCEPTION_INIT(INTERNAL_BAD_KEY, -28231);

/*

|| Local exception to hide Oracle -28232 Error Code.

*/

INTERNAL_BAD_DATA exception;

PRAGMA EXCEPTION_INIT(INTERNAL_BAD_DATA, -28232);

/*

|| Local function to get the encryption key for a particular case.

*/

FUNCTION get_case_encryption_key(pi_cas_id IN ELS_CASES.ID%TYPE) RETURN RAW IS

/*

|| The key to be returned.

*/

key RAW(16);

/*

|| Cursor to return the case encyption key in encrypted format.

*/

CURSOR c_case_key(b_cas_id ELS_CASES.ID%TYPE) IS

SELECT encryption_key

FROM els_cases

WHERE id = b_cas_id;

BEGIN

OPEN c_case_key(pi_cas_id);

FETCH c_case_key INTO key;

CLOSE c_case_key;

RETURN key;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE NO_CASE;

END;

/*

|| Procedure to initialize package with the master key.

|| The master key will be held elsewhere from the database.

*/

PROCEDURE set_master_key(pi_key IN RAW) IS

BEGIN

IF LENGTHB(pi_key) != 32 THEN

RAISE BAD_KEY;

END IF;

ps_encryption_key := pi_key;

END;

/*

|| Procedure to initialize package with the master key.

|| Always returns 'Y'

|| The master key will be held elsewhere from the database.

*/

FUNCTION set_master_key(pi_key IN RAW) RETURN VARCHAR2 IS

BEGIN

set_master_key(pi_key);

RETURN 'Y';

END;

/*

|| Procedure to initialize package with the case encryption key.

*/

PROCEDURE set_case_key(pi_master_key IN RAW,

pi_cas_id IN ELS_CASES.ID%TYPE) IS

BEGIN

ps_encryption_key := pi_master_key;

ps_encryption_key := decrypt(pi_data=>get_case_encryption_key(pi_cas_id));

END;

/*

|| Function to initialize package with the case encryption key.

|| Always returns 'Y'

*/

FUNCTION set_case_key(pi_master_key IN RAW,

pi_cas_id IN ELS_CASES.ID%TYPE) RETURN VARCHAR2 IS

BEGIN

set_case_key(pi_master_key,pi_cas_id);

RETURN 'Y';

END;

/*

|| Function to encrypt data using the master key. Note the length of

|| pi_data, in bytes, must be at most 2000 bytes and be divisible by 8.

*/

FUNCTION encrypt(pi_data IN RAW) RETURN RAW IS

BEGIN

RETURN dbms_obfuscation_toolkit.DES3Encrypt(input => pi_data,

key => ps_encryption_key);

EXCEPTION

WHEN INTERNAL_BAD_DATA THEN

RAISE BAD_DATA;

WHEN INTERNAL_BAD_KEY THEN

RAISE BAD_KEY;

END;

/*

|| Function to encrypt a BLOB using the current encryption key.

*/

FUNCTION encrypt(pi_blob IN BLOB) RETURN BLOB IS

/*

|| Temporary blob variable to hold the encrypted contents.

*/

result blob;

/*

|| Variable to hold the length of the blob.

*/

blob_length PLS_INTEGER := dbms_lob.getlength(pi_blob);

/*

|| The Oracle encryption routines can only encrypt data whose length is <=2000.

*/

max_chunk_length PLS_INTEGER := 2000;

/*

|| Variable to hold the length of the current chunk that is being encrypted.

*/

chunk_length PLS_INTEGER;

/*

|| Variable to remember which how much of the input blob has been encrypted.

*/

pointer PLS_INTEGER := 1;

/*

|| Variable to hold the next bit of data to be encrypted.

*/

chunk RAW(2000);

/*

|| Variable to hold a pad byte used to pad the last chunk.

*/

pad RAW(1) := utl_raw.substr(utl_raw.cast_to_raw('0'),1,1);

BEGIN

/*

|| Create the temporary blob using the database memory buffer.

*/

dbms_lob.createtemporary(result, TRUE, dbms_lob.call);

/*

|| Loop through the input blob

*/

WHILE (pointer <= blob_length) LOOP

/*

|| Grab at most 2000 bytes from the input blob.

*/

chunk_length := LEAST(max_chunk_length,blob_length-pointer+1);

chunk := dbms_lob.substr(pi_blob,chunk_length,pointer);

/*

|| Pad any chunk (ie the last) so its length is divisible by 8 (another Oracle limitation on encryption)!.

*/

WHILE mod(chunk_length,8) !=0 LOOP

chunk := utl_raw.concat(chunk,pad);

chunk_length := chunk_length+1;

END LOOP;

/*

|| Encrypt the chunk and write it to the end of the temporary blob.

*/

dbms_lob.writeappend(result,

chunk_length,

encrypt(pi_data => chunk)

);

/*

|| Advance the pointer by the length of the last chunk.

*/

pointer := pointer + chunk_length;

END LOOP;

/*

|| All Done!

*/

RETURN result;

END;

/*

|| Function to decrypt data using the master key. Note the length of

|| pi_data, in bytes, must be at most 2000 bytes and be divisible by 8.

*/

FUNCTION decrypt(pi_data IN RAW) RETURN RAW IS

BEGIN

RETURN dbms_obfuscation_toolkit.DES3Decrypt(input => pi_data,

key => ps_encryption_key);

EXCEPTION

WHEN INTERNAL_BAD_DATA THEN

RAISE BAD_DATA;

WHEN INTERNAL_BAD_KEY THEN

RAISE BAD_KEY;

END;

/*

|| Function to decrypt a BLOB using the current encryption key.

*/

FUNCTION decrypt(pi_blob IN BLOB,

pi_size IN PLS_INTEGER) RETURN BLOB IS

/*

|| Temporary blob variable to hold the encrypted contents.

*/

result BLOB;

/*

|| Variable to hold the length of the blob.

*/

blob_length PLS_INTEGER := dbms_lob.getlength(pi_blob);

/*

|| The Oracle encryption routines can only encrypt data whose length is <=2000.

*/

max_chunk_length PLS_INTEGER := 2000;

/*

|| Variable to hold the length of the current chunk that is being encrypted.

*/

chunk_length PLS_INTEGER;

/*

|| Variable to remember which how much of the input blob has been encrypted.

*/

pointer PLS_INTEGER := 1;

BEGIN

/*

|| Create the temporary blob using the database memory buffer.

*/

dbms_lob.createtemporary(result, TRUE, dbms_lob.call);

/*

|| Loop through the input blob

*/

WHILE (pointer <= blob_length) LOOP

/*

|| Grab at most 2000 bytes from the input blob.

*/

chunk_length := LEAST(max_chunk_length,blob_length-pointer+1);

/*

|| Decrypt the chunk and write it to the end of the temporary blob.

*/

dbms_lob.writeappend(result,

chunk_length,

decrypt(pi_data => dbms_lob.substr(pi_blob,

chunk_length,

pointer

)

)

);

/*

|| Advance the pointer by the length of the last chunk.

*/

pointer := pointer + chunk_length;

END LOOP;

/*

|| Remove the padding bytes that were added when the data was encrypted.

*/

dbms_lob.trim(result,pi_size);

/*

|| All Done!

*/

RETURN result;

END;

/*

|| Procedure to clear session state of stored keys.

*/

PROCEDURE CLEAR IS

BEGIN

ps_encryption_key:=null;

END;

END;

/

and here is the PL/sql I run before running the sql stmt

DECLARE

mkey LONG RAW;

BEGIN

mkey := UTL_RAW.CAST_TO_RAW ('&&key');

my_encryption.set_master_key(mkey);

my_encryption.set_case_key(mkey,&&case_id);

END;

mkey is a 16 digit key .

and the encrypted_contents I'm trying to decrypt is a BLOB.

select my_encryption.decrypt(encrypted_contents,file_size),mime_type

from my_drafts where id = &&draft_id;

I hope this makes sense .

Ragini