Is It Possible To use regular expression redaction policy to NCHAR, NVARCHAR2 DATATYPE on Oracle Database 12c Release1 (12.1) ?

Last updated on MARCH 13, 2017

Applies to:

Goal

Is it possible to use regular expression redaction policy to NCHAR, NVARCHAR2 DATATYPE on Oracle Database 12c Release1 (12.1) ?

When executing sample script, It seems that full redaction is performed instead of regular expression redaction.

SQL> connect scott/tiger
Connected.
SQL> drop table test;

Table dropped.

SQL> create table test (c1 nchar(5));

Table created.

SQL> insert into test values ('12345');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> connect / as sysdba
Connected.
SQL>
SQL> BEGIN
2 DBMS_REDACT.ADD_POLICY(OBJECT_SCHEMA => 'SCOTT',
3 object_name => 'TEST',
4 policy_name =>'SYSTEM_REDACT',
5 COLUMN_NAME => 'C1', FUNCTION_TYPE => DBMS_REDACT.REGEXP,
6 FUNCTION_PARAMETERS => NULL,
7 expression => '1=1',
8 REGEXP_PATTERN => '(\d\d\d)(\d\d)',
9 REGEXP_REPLACE_STRING => '\2\1',
10 REGEXP_POSITION => 1);
11 END;
12 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> Rem In the above regexp redaction policy, we are replacing the pattern
SQL> Rem '(\d\d\d)(\d\d)' with '\2\1'. An input string '12345' should be replaced
SQL> Rem by '45123'.
SQL>
SQL> connect scott/tiger
Connected.
SQL> select c1 from test;

C1
--------------------------------------------------------------------------------

* Null data means that full redaction is performed.

Solution