Compute flea hash in trigger on blob table

Question

My environment is Oracle 11g. I have a T_IMG table whose columns are an image frame, an image identifier, and an image hash calculated 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. To do this, I use the T_IMG table before the trigger when inserting or updating the table, but it is difficult for me to access the blob column of the image inside the trigger.

CREATE TABLE TEST.T_IMG ( IDN VARCHAR2(18 BYTE) NOT NULL, IMG BLOB NOT NULL, IMGHASH VARCHAR2(128 BYTE) ); 

The SHA256 hash for strings and blobs is calculated using the following code, based on the example of Sean Stueber at http://seanstuber.wordpress.com/2012/03/22/using-java-to-extend-dbms_crypto/

 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'; / 

The above features work. Testing them, I get:

 SELECT sha2_string('0123456789',256) FROM DUAL; SELECT sha2_blob(utl_raw.cast_to_raw('0123456789'),256) FROM DUAL; 

SHA2_STRING ('0123456789', 256)
-------------------------------------------------- ------------------------------ 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882
1 row selected.

SHA2_BLOB (UTL_RAW.CAST_TO_RAW ('0123456789'), 256)
-------------------------------------------------- ------------------------------ 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882
1 row selected.

I use an insert or update on T_IMG before the trigger to calculate the hash of the SH256 image column.

 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; / 

The trigger itself works; where it calculates and inserts the hash value on the data layout (manufactured string or blob), but not on the actual image blob data, when it produces the following error:

ORA-29532: Java call terminated with an uncaught Java exception: java.sql.SQLException: invalid empty operation
ORA-06512: on page "TEST.SHA2_BLOB", line 1
ORA-06512: on page "TEST.TR_IMG_UPSERT", line 13
ORA-04088: an error occurred while starting the trigger "TEST.TR_IMG_UPSERT"

The trigger seems to be reading data: new.IMG as an empty blob .

Why is this happening and how can I solve my problem of automatically calculating and inserting an image hash into the T_IMG table every time a row is inserted or updated in the T_IMG table?

Note. A hash cannot be provided by the client application by inserting / updating data rows.

ADIM

Answer 1

I executed the following code in Oracle PL / SQL:

 var idn varchar2(18 byte); exec :idn := '0'; insert into t_img (idn, img) values (:idn, utl_raw.cast_to_raw('0123456789') ); commit; select idn, dbms_lob.getlength(img) as length, imghash from t_img where idn=:idn; update t_img set img=utl_raw.cast_to_raw('012345678901') where idn=:idn; commit; select idn, dbms_lob.getlength(img) as length, imghash from t_img where idn=:idn; 

and he works

SQL * Plus: Release 11.2.0.1.0 Production on Sat Aug 31 11:20:04 am 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

The PL / SQL procedure completed successfully.

1 row created. Complete completion.

IDENTIFICATION LENGTH IDENTIFICATION

---

0 10 84D89877F0D4041EFB6BF91A16F0248F2FD573E6AF05C19F96BEDB9F882F7882

1 row updated. Complete completion.

IDENTIFICATION LENGTH IDENTIFICATION

---

0 12 5CE9AB10EA74270D12620DFACD74D262C6411E20761E459BB1B265DE883422AC

I found out that the error I sent occurs only after I registered the manually edited IMG blob field in Toad (11.6.0.43 free version), but not when I use the table (inserts, updates) via PL / SQL .

ADIM

Answer 2

I had to do this trick:

 create or replace TRIGGER DESPACHO_ACTA_ARCHIVO_IU BEFORE INSERT OR UPDATE ON CAT_DESPACHO_ACTA_ARCHIVO FOR EACH ROW DECLARE TEMPFLD BLOB; blobid number; BEGIN SELECT BLOBTEMP_SEQ.NEXTVAL INTO blobid FROM SYS.DUAL; INSERT INTO blobtemp VALUES (:new.bacta,blobid); SELECT blobfield INTO :new.bacta FROM blobtemp where id =blobid ; delete from blobtemp where id =blobid ; :NEW.SHEX_SHA1 := sha2_blob(:new.bacta,1); END; 

Create a temporary table to force the blob to persist and get the correct hash value