PLSQL BLOB MD5 Checksum

Using Oracle PL/SQL, I created a function to calculate the MD5 checksum for a Oracle BLOB. This calculation matches the Content MD5 Hash that one finds in the file properties at Oracle Cloud Infrastructure Storage (OCI Storage or OCI Bucket). You’ll find this on my Github page:

https://github.com/cmoore-sp/blob_md5_checksum

For those need an MD5 checksum of a string (varchar2), please take a look at an APEX Utility for MD5 checksum for Varchar2.

I could not find an similar function for MD5 hash for BLOB. I wanted to calculate the MD5 hash for a blob prior to the upload to OCI so we can compare the initial calculation with the value returned from OCI in the response headers. If the two values match, then we can be assured that the BLOB contents at both ends of the transfer match.

Here is a function to calculate an MD5 Hash Checksum on a BLOB

OCI requires Base64 encoding with the value returned as a string (varchar2).

To capture the response headers, you’ll need to adopt code that loops through APEX_WEB_SERVICE.G_HEADERS
Not much has been provided on the Oracle documentation about this array. Here are two articles I found (JULY 2021)

Article 1

Article 2

The sample code below captures the OCI calculated MD5.

We tend to capture the request headers and response headers in an API Staging table. When APIs go well, they do so very well.
When they mess up, you need all of the tools and information you can find.

procedure response_headers (
    r_bucket_object     in out api_bucket_object%rowtype,
    r_staging       in out api_staging%rowtype
    )
as
begin
    for i in 1.. apex_web_service.g_headers.count loop
        r_staging.response_headers := r_staging.response_headers || apex_web_service.g_headers(i).name||':';
        r_staging.response_headers := r_staging.response_headers || apex_web_service.g_headers(i).value || lf;
        case 
            when apex_web_service.g_headers(i).name = 'etag' then
                r_bucket_object.object_etag     := apex_web_service.g_headers(i).value;
            when apex_web_service.g_headers(i).name = 'opc-content-md5' then                
                r_bucket_object.remote_content_md5  := apex_web_service.g_headers(i).value;
            when apex_web_service.g_headers(i).name = 'version-id' then 
                r_bucket_object.object_version_id   := apex_web_service.g_headers(i).value;
            else
                null;
        end case;
    end loop;   
end response_headers;

Function BLOB_MD5_CHECKSUM

function blob_md5_checksum (
	P_BLOB			in blob
	) return varchar2
as
	------------------------------------------------------------------------------
	-- Function: 	blob_md5_checksum written in Oracle PLSQL
	-- Author:		Christina Moore
	-- Date:			30 JUL 2021
	-- Version:		1
	--
	-- MD5 hash checksum on the blob provided
	-- This hash matches the content hash on OCI Objects
	--
	--
	/*
	initial_content_md5			:= blob_md5_checksum(f.blob_content);		
	*/
	------------------------------------------------------------------------------
	l_return									varchar2(2000);
	l_base64									varchar2(2000);
	l_hash										raw(2000);
begin
	l_hash := dbms_crypto.hash(
		src => P_BLOB,
		typ => dbms_crypto.hash_md5
		);
								
	l_base64 := utl_encode.base64_encode(l_hash);
	l_return := utl_raw.cast_to_varchar2(l_base64);
	return l_return;
end blob_md5_checksum;

Keywords:

Oracle PL/SQL, PLSQL MD5 checksum, PLSQL Blob MD5 checksum, PL/SQL Blob MD5 checksum calculation