CODEWITHSUNDEEP
oracle-databaseencryptionplsqldbms-crypto
Dantel35
Dantel35

Reputation: 135

Using DBMS_CRYPTO.ENCRYPT safely - NLS error detected

I am trying to use DBMS_CRYPTO encrypt, but no matter how I choose some of the parameters, I encounter values for which the code throws

ORA-01890: NLS error detected ORA-06512: at "SYS.UTL_I18N", line 72 ORA-06512: at "SYS.UTL_I18N", line 353 ORA-06512: at line 26

Here is an example

declare
-- v_value             VARCHAR2 (4000) := '9Ab2Ov1Bd4'; --  works
-- v_value             VARCHAR2 (4000) := '7Md4Mt7Gk0'; --  works
-- v_value             VARCHAR2 (4000) := '3Vf8Fi2Pa5'; --  works
-- v_value VARCHAR2(4000) := '5Vq2Dc4Cq9'; -- works as well
v_value VARCHAR2(4000) := '2Cq0Yh3Vb2'; --this does not work?
v_result            VARCHAR2 (4000);
v_raw_row           RAW (2000);                 -- stores ec binary text

v_enc_type          PLS_INTEGER
         :=                        
     DBMS_CRYPTO.ENCRYPT_AES256 +  
     DBMS_CRYPTO.CHAIN_CBC +
     DBMS_CRYPTO.PAD_ZERO;                   -- total encryption type

v_def_k             VARCHAR2 (32) := 'mMbSmSMr_!_uAlns9asG5_a_AfhS4_3a';
begin
 v_raw_row :=
            DBMS_CRYPTO.ENCRYPT (
               src   => UTL_I18N.STRING_TO_RAW (v_value, 'AL32UTF8'),
               typ   => v_enc_type,
               key   => UTL_RAW.CAST_TO_RAW (v_def_k));
v_result := UTL_I18N.RAW_TO_CHAR (v_raw_row, 'AL32UTF8');
dbms_output.put_line(v_result);
end;

Changing the encryption type and key "solves" the issue for the one value which does not work, but it I always encounter another value which will then throw this exact exception.

I tried this in Oracle 12.2.0.1.0 as well as in 19.0.0.0.0. Exactly the same behaviour.

I guess that something I am doing is completely wrong. Any help is appreciated.

Upvotes: 0

Views: 1290

Answers (1)

little_amb
little_amb

Reputation: 94

Instead of AL32UTF8 as source character set, can you please try using WE8ISO8859P1 or null.

From Oracle doc:

UTL_I18N.RAW_TO_CHAR is a function that converts raw to char data type conversion can be implemented, but not different character sets can be converted. The second parameter should specify which characteret is passed by the raw data, ie the source character set.

Specify the character set that the RAW data was derived from. If src_charset is NULL, then the database character set is used.

so if using DB characterset or NULL, no error will be reported.

Upvotes: 1

Related Questions