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).

For those need an MD5 checksum of a string (varchar2), please take a look at the APEX Utility 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;

Keywords:

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