BLOB BFILE SHA256 calculation from Oracle Database using PL/SQL SQL by way of Java stored procedure

The code below demonstrates a method to calculate from the Oracle Database an SHA-256 checksum of a BLOB or BFILE by way of a Java stored procedure which in turn can be triggered from a SQL DML statement or PL/SQL block.

REM -- we don't want the ampersand in source below interpreted
SET DEFINE OFF


CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "IOUtils" AS
/* MShannon 2019 */
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;

import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

import oracle.jdbc.OracleBfile;
import oracle.sql.BLOB;

public class IO
{
public static String getSHA256ChecksumHexEncoded(OracleBfile b) throws Exception
{
InputStream is = null;
try
{
is = new BufferedInputStream(b.getBinaryStream());

return getSHA256ChecksumHexEncoded(is);
}
finally
{
streamClose(is);
}
}

public static String getSHA256ChecksumHexEncoded(BLOB b) throws Exception
{
InputStream is = null;
try
{
is = new BufferedInputStream(b.getBinaryStream());

return getSHA256ChecksumHexEncoded(is);
}
finally
{
streamClose(is);
}
}

public static String getSHA256ChecksumHexEncoded(InputStream is) throws IOException
{
byte[] bytes = getDigest(is, "SHA-256");

return toHex(bytes);
}

public static byte[] getDigest(InputStream is, String algorithm) throws IOException
{
byte[] bytes = new byte[262144];
MessageDigest md = null;
try
{
md = MessageDigest.getInstance(algorithm);

int bytesRead = 0;
do
{
bytesRead = is.read(bytes);

if (bytesRead > 0)
{
md.update(bytes, 0, bytesRead);
}
}
while (bytesRead != -1);

return md.digest();
}
catch (NoSuchAlgorithmException e)
{
String msg = String.format("Failed to compute the checksum. No such algorithm %s. Error: %s", algorithm,
e.getMessage());

throw new Error(msg, e);
}
}

public static void streamClose(InputStream in)
{
if (in != null)
{
try
{
in.close();
}
catch (IOException ignore)
{
}
}
}

public static String toHex(byte[] bytes)
{
if (bytes == null)
{
return null;
}

StringBuilder sb = new StringBuilder(bytes.length * 2);
for (int i = 0; i < bytes.length; i++)
{
sb.append(Character.forDigit((bytes[i] & 0xf0) >> 4, 16));
sb.append(Character.forDigit(bytes[i] & 0x0f, 16));
}

return sb.toString();
}
}
/

CREATE OR REPLACE FUNCTION hash_sha256_bfile (p_bfile in BFILE) RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'IO.getSHA256ChecksumHexEncoded(oracle.jdbc.OracleBfile) return String';
/

CREATE OR REPLACE FUNCTION hash_sha256_blob (p_blob in BLOB) RETURN VARCHAR2 AS LANGUAGE JAVA
NAME 'IO.getSHA256ChecksumHexEncoded(oracle.sql.BLOB) return String';
/


SET DEFINE ON


In the example use-case below we calculate an SHA-256 checksum of the file /etc/hosts present on the Database server. We first try SHA-256 checksum calculation direct from a BFILE.  We subsequently perform SHA-256 checksum calculation using a BLOB.

REM -- AS APPROPRIATE PRIVILEGED USER (e.g DBA) - CREATE DIRECTORY OBJECT
CREATE DIRECTORY FILEUPLOADS AS '/etc';
REM -- FOR TESTING ONLY (NOT FOR PRODUCTION) ALLOW EVERYONE TO READ DIR FILES

GRANT READ ON DIRECTORY FILEUPLOADS TO public;

SET SERVEROUTPUT ON

DECLARE
  l_bfile BFILE := BFILENAME('FILEUPLOADS', 'hosts');
  l_result VARCHAR2(64);
BEGIN
  DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.LOB_READONLY);

  SELECT hash_sha256_bfile(l_bfile) INTO l_result FROM dual;
  DBMS_OUTPUT.PUT_LINE('Result=' || l_result);

  -- Close lob objects
  DBMS_LOB.CLOSE(l_bfile);
END;
/

DECLARE
  l_bfile BFILE := BFILENAME('FILEUPLOADS', 'hosts');
  l_blob BLOB;
  l_result VARCHAR2(64);
BEGIN
  DBMS_LOB.FILEOPEN(l_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.CREATETEMPORARY(l_blob,TRUE, DBMS_LOB.SESSION);

  DBMS_LOB.LOADFROMFILE(
        dest_lob => l_blob
      , src_lob  => l_bfile
      , amount   => DBMS_LOB.LOBMAXSIZE
      , dest_offset   => 1
      , src_offset   => 1);

  SELECT hash_sha256_blob(l_blob) INTO l_result FROM dual;
  DBMS_OUTPUT.PUT_LINE('Result=' || l_result);

  -- Close lob objects
  DBMS_LOB.CLOSE(l_bfile);
  DBMS_LOB.FREETEMPORARY(l_blob);
END;
/

I hope this helps someone!