title: Compute blob hash in trigger on blob's table
body: <p>My environment is Oracle 11g. I have a table T_IMG whose columns are an image blob, an image identifier, and the image's hash computed as SHA256. I want the image hash to be computed and inserted into the T_IMG table every time a row is inserted or updated in the T_IMG table. For this I use a before trigger on insert or update on the T_IMG table but I have difficulty accessing the image blob column inside the trigger.</p>
  
  <pre><code>CREATE TABLE TEST.T_IMG
  (
      IDN       VARCHAR2(18 BYTE)      NOT NULL,
      IMG       BLOB                   NOT NULL,
      IMGHASH   VARCHAR2(128 BYTE)
  );
  </code></pre>
  
  <p>The SHA256 hash for strings and blobs is computed by the following code that is based on Sean Stuber's example at <a href="http://seanstuber.wordpress.com/2012/03/22/using-java-to-extend-dbms_crypto/" rel="noreferrer">http://seanstuber.wordpress.com/2012/03/22/using-java-to-extend-dbms_crypto/</a> </p>
  
  <pre><code>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';
  /
  </code></pre>
  
  <p>The above functions work. Testing them I get:</p>
  
  <pre><code>SELECT sha2_string('0123456789',256) FROM DUAL;
  SELECT sha2_blob(utl_raw.cast_to_raw('0123456789'),256) FROM DUAL;
  </code></pre>
  
  <blockquote>
    <p>SHA2_STRING('0123456789',256)<br>
    -------------------------------------------------------------------------------- 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882<br>
    1 row selected.</p>
    
    <p>SHA2_BLOB(UTL_RAW.CAST_TO_RAW('0123456789'),256)<br>
    -------------------------------------------------------------------------------- 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882<br>
    1 row selected.</p>
  </blockquote>
  
  <p>I use a before trigger on insert or update on T_IMG to compute the SH256 hashing of the image column. </p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TEST.TR_IMG_UPSERT
  BEFORE INSERT OR UPDATE
  ON TEST.T_IMG
  
  FOR EACH ROW
  DECLARE 
      imghash varchar2(128);
      vblob blob;
  BEGIN
  
      vblob := :new.IMG;
  
      IF(vblob IS NOT NULL)THEN
          /*-- this works
          imghash := sha2_string('0123456789',256); 
          :new.imghash := imghash; */
  
          /*-- this works too
          vblob := utl_raw.cast_to_raw('0123456789');
          imghash := sha2_blob(vblob,256);
          :new.imghash := imghash; */
  
          -- this DOES NOT work
          imghash := sha2_blob(vblob,256);
          :new.imghash := imghash;
  
      END IF;
  
  END;
  /
  </code></pre>
  
  <p>The trigger by itself works; where it computes and inserts the hash value on mock-up data (manufactured string or blob) but not on the real image blob data when it throws the following error:</p>
  
  <blockquote>
    <p>ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException: Invalid empty lob operation<br>
    ORA-06512: at "TEST.SHA2_BLOB", line 1<br>
    ORA-06512: at "TEST.TR_IMG_UPSERT", line 13<br>
    ORA-04088: error during execution of trigger 'TEST.TR_IMG_UPSERT'</p>
  </blockquote>
  
  <p><strong>It seems that the trigger reads the :new.IMG data as an empty blob</strong>. </p>
  
  <p>Why this happens and how can I solve my problem of automatically computing and inserting the image's hash into the T_IMG table every time a row is inserted or updated in the T_IMG table?</p>
  
  <p><em>Note: The hash can not be provided by the client app inserting/updating the data rows.</em></p>
  
  <p>AdiM</p>