Error Logging For DMLs Executed On Encrypted Tables Fails With ORA-38906 (Doc ID 984700.1)

Last updated on JANUARY 04, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7
Information in this document applies to any platform.

Symptoms

The target table of a merge operation contains an encrypted column. When the merge operation causes record to be logged in the error log table, ORA-38906 is raised:

SQL> CREATE TABLE T_TARGET(F1 VARCHAR2(10) NOT NULL ,
                           F2 VARCHAR2(35) NOT NULL ,
                           WL_1 VARCHAR2(9),
                           WL_SCR_1 NUMBER(4,3) ENCRYPT USING 'AES128');

Table created.

SQL> CREATE TABLE T_SOURCE(F1 VARCHAR2(10) NOT NULL,
                           F2 VARCHAR2(35) NULL ,
                           WL_1 VARCHAR2(9),
                           WL_SCR_1 NUMBER(4,3));

Table created.

SQL> BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'T_TARGET');
END;
/

PL/SQL procedure successfully completed.

SQL> INSERT INTO T_SOURCE VALUES ('F1_01',null,'WL_01',1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> MERGE INTO T_TARGET T USING (
     SELECT F1,F2,WL_1,WL_SCR_1 FROM T_SOURCE) S
     ON (T.F1 = S.F1)
     WHEN MATCHED THEN
      UPDATE SET T.F2 = S.F2,T.WL_1 = S.WL_1,T.WL_SCR_1 = S.WL_SCR_1
     WHEN NOT MATCHED THEN
      INSERT (T.F1,T.F2,T.WL_1,T.WL_SCR_1)
      VALUES (S.F1,S.F2,S.WL_1,S.WL_SCR_1)
     LOG ERRORS INTO ERR$_T_TARGET ('MERGE') REJECT LIMIT UNLIMITED;
     
INSERT (T.F1,T.F2,T.WL_1,T.WL_SCR_1) VALUES (S.F1,S.F2,S.WL_1,S.WL_SCR_1)
*
ERROR at line 7:
ORA-38906: insert into DML Error Logging table "ERR$_T_TARGET" failed
ORA-01400: cannot insert NULL into ("SCOTT"."T_TARGET"."F2")


This error can occur when executing any DML as long as the target table is encrypted. The error occurs occurs when using either column level TDE or tablespace encryption.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms