Am using the below Function to decrypt a code ? Could any help to resolve?

Question

function ibp_crypto_decrypt ( p_session_id in RAW) return raw is
l_decrypted_raw     RAW(2048);
l_encrypted_raw     RAW(2048) := p_session_id;
l_key               RAW(128) := UTL_RAW.cast_to_raw('abcdefgh');
begin
dbms_output.put_line('l_encrypted_raw '||'='||l_encrypted_raw);
l_decrypted_raw := DBMS_CRYPTO.decrypt(src => l_encrypted_raw, 
                                       typ => DBMS_CRYPTO.des_cbc_pkcs5, 
                                       key => l_key); 
DBMS_OUTPUT.put_line('Decrypted : ' || UTL_RAW.cast_to_varchar2(l_decrypted_raw))   ;                                      
return l_decrypted_raw;--RAWTOHEX(UTL_RAW.cast_to_varchar2(l_decrypted_raw));                                          
exception when others
then
dbms_output.put_line(' ibp_crypto_decrypt'||'='||sqlerrm||dbms_utility.format_error_backtrace);
end;                                         

ibp_crypto_decrypt=ORA-28817: PL/SQL function returned an error.

ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "IBOXV5_TEST.IBK_CRYPTO_ENCRYPTION", line 28

Answer 1

I've completed your example with the encryption of an actual value and it works as expected. Are you sure you correctly encrypted the value ?

DECLARE
    l_input VARCHAR2(30) := utl_raw.cast_to_raw('12345678');
    l_encrypted_raw RAW(2048);
    l_decrypted_raw RAW(2048);
    l_key RAW(128) := utl_raw.cast_to_raw('abcdefgh');
BEGIN
    l_encrypted_raw := dbms_crypto.encrypt(src=>l_input, typ=>dbms_crypto.des_cbc_pkcs5, key=>l_key);
    l_decrypted_raw := dbms_crypto.decrypt(src=>l_encrypted_raw, typ=>dbms_crypto.des_cbc_pkcs5, key=>l_key);
    dbms_output.put_line('Decrypted : ' || utl_raw.cast_to_varchar2(l_decrypted_raw));
END;
/

Answer 2

Oracle support said:

When you are passing the encrypted value to the decrypt function or for storage within database, do not use UTL_I18N.RAW_TO_CHAR instead use RAWTOHEX or UTL_ENCODE.BASE64_ENCODE. Similarly for converting back to raw do not use UTL_I18N.STRING_TO_RAW instead use HEXTORAW or UTL_ENCODE.BASE64_DECODE.

So, the answer depends on how you create RAW value that is passed to your function. From my experience in Oracle 12.2 using UTL_RAW.cast_to_raw function also sometimes causes ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67 in decryption function.