Hello,
In 19c we have a table with a BLOB column supposed to store scrambled XML data; the Java app calls a PL/SQL procedure that does an insert in this table, with a CLOB containing the full XML string to be encrypted. It's been working for ages in 11.2.
But now we upgraded this DB to 19c and sometimes (we haven't been able to reproduce at will, it happens on an irregular basis - randomly?, we're not sure) the BLOB stored is only 8-byte long ('9D5A4EC533AF526D' - that once decrypted with same key returns as a null string) instead of having a length of 1500 to 2000 bytes as it happens most of the time, depending on XML size.
It's implemented like this:
insert into tabbn(
...
values (
ID,
custom_encrypt.encrypt(P_XML_string, P_KEY)
with ENCRYPT() function in CUSTOM_ENCRYPT pkg showing:
PACKAGE BODY custom_encrypt as
r_blb BLOB;
[...]
function encrypt(XML_string CLOB) return blob as
begin
[...]
DBMS_LOB.CREATETEMPORARY(r_blb...);
...
dbms_crypto.encrypt(dst => r_blb, src=> XML_string, typ => our_type, key => l_key, iv =>null);
return r_blb;
[...]
It only occurs when called from the Java application, we never could reproduce this in pure SQL/PL-SQL, even in a loop with thousands of trials: the XML clob may be wrongly encrypted to this empty (once re-decrypted) '9D5A4EC533AF526D' data when invoked from the application, randomly (or so it seems, no certainty...).
As we haven't been able to figure out how this can happen, and as we have no case for MOS because we can't reproduce at will, we're beginning to consider to implement a low-level trace of Oracle binaries (with oradebug?) in the background, to try to see when our XML string is wrongly scrambled.
My question: is it possible? Is there a way to tell either Oracle or the OS to truss or strace what happens behind the scene when DBMS_CRYPTO.ENCRYPT() is called?
Thanks a lot in advance for any suggestion in that regard...
Cheers,
Sebino