Data Redaction returns NULL or Zero values in some scenarios (Doc ID 1677218.1)

Last updated on NOVEMBER 22, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Symptoms

In some scenarios, data redaction returns NULL or Zero values instead of the partially redacted data pattern.

An example of partial redaction is below :

-- Observe the last 3 rows of the table EMPLOYEES

SQL> select EMPNO, ENAME from test_redact.EMPLOYEES;

     EMPNO ENAME
---------- ----------
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER
       123 AR
       123 A
       123 ARA


-- Observe the execution of REGEXP_REPLACE on the table data.


SQL> SELECT empno, REGEXP_REPLACE(ename,'[A-Za-z0-9]','x',3, 0, 'i') ename
FROM test_redact.EMPLOYEES;

     EMPNO ENAME
---------- ---------------
      7788 SCxxx
      7839 KIxx
      7844 TUxxxx
      7876 ADxxx
      7900 JAxxx
      7902 FOxx
      7934 MIxxxx
       123 AR
       123 A
       123 ARx

-- Partial redaction policy is applied.


SQL> begin
  2  DBMS_REDACT.ADD_POLICY(
  3  object_schema => 'TEST_REDACT'
, object_name => 'EMPLOYEES'
  4    5  , column_name => 'ENAME'
  6  , policy_name => 'ename_test'
  7  , function_type => DBMS_REDACT.REGEXP
, expression => '1=1'
, regexp_pattern => '[A-Za-z0-9]'
  8    9   10  , regexp_replace_string =>'x'
 11  , regexp_position => 3
 12  , regexp_occurrence => 0
 13  , regexp_match_parameter => 'i'
 14  );
 15  end;
 16  /

PL/SQL procedure successfully completed.


-- Observe the NULL values returned for two rows.


SQL> SELECT empno,ename FROM employees;

     EMPNO ENAME
---------- ---------------
      7788 SCxxx
      7839 KIxx
      7844 TUxxxx
      7876 ADxxx
      7900 JAxxx
      7902 FOxx
      7934 MIxxxx
       123
       123
       123 ARx



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