My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.