My Oracle Support Banner


Last updated on JULY 02, 2021

Applies to:

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


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.  

After the redaction policy is applied the reports which use the redacted
columns  are throwing the below error .Obiee report query forms with WITH
clause which is using CONCAT,TRIM,SUM functions for the redacted column which
is causing the issue

State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 28094,
message: ORA-28094: SQL construct not supported by data redaction at OCI call

OCIStmtExecute. (HY000)

Try Using redacted column(FULL redaction type) in below clause,

with table2 AS (
 select concat(column1,' ') from table1 )
 select *  from table2; -- This gives error where 'column1' is
redacted column

Select concat(column1,' ') from table1 ;  -- This works




1.Create User in sysdba of RDBMS,
    create user abc identified by xxx default tablespace users temporary tablespace temp;
    grant connect, resource to abc;
    grant unlimited tablespace to abc;

2.Then grant user with below mentioned policies,
    grant select on sys.redaction_policies to abc;
    grant select on sys.redaction_columns to abc;
    grant execute on dbms_redact to abc;

3.Now Login to new Schema i.e abc and create new table as mentioned below,
    CREATE TABLE payment_details (
      id          NUMBER       NOT NULL,
      customer_id NUMBER       NOT NULL,
      card_no     NUMBER       NOT NULL,
      card_string VARCHAR2(19) NOT NULL,
      expiry_date DATE         NOT NULL,
      sec_code    NUMBER       NOT NULL,
      valid_date  DATE,
      CONSTRAINT payment_details_pk PRIMARY KEY (id)

    INSERT INTO payment_details VALUES (1, 4000,1234123412341234, '1234-1234-1234-1234', TRUNC(ADD_MONTHS(SYSDATE,12)), 123, NULL);
    INSERT INTO payment_details VALUES (2, 4001,2345234523452345, '2345-2345-2345-2345', TRUNC(ADD_MONTHS(SYSDATE,12)), 234, NULL);
    INSERT INTO payment_details VALUES (3, 4002,3456345634563456, '3456-3456-3456-3456', TRUNC(ADD_MONTHS(SYSDATE,12)), 345, NULL);
    INSERT INTO payment_details VALUES (4, 4003,4567456745674567, '4567-4567-4567-4567', TRUNC(ADD_MONTHS(SYSDATE,12)), 456, NULL);
    INSERT INTO payment_details VALUES (5, 4004,5678567856785678, '5678-5678-5678-5678', TRUNC(ADD_MONTHS(SYSDATE,12)), 567, NULL);

SELECT * FROM  payment_details;

4.Apply Full Redaction Policy on column name 'card_no'

    object_schema => 'abc',
    object_name   => 'payment_details',
    column_name   => 'card_no',
    policy_name   => 'redact_card_info',
    function_type => DBMS_REDACT.full,
    expression    => '1=1'

/* SELECT object_owner, object_name,policy_name,expression,enable,policy_description FROM   redaction_policies where object_owner='ABC';
SELECT object_owner,object_name,column_name,function_type,function_parameters,regexp_pattern,regexp_replace_string,regexp_position,regexp_occurrence,regexp_match_parameter,column_description
FROM redaction_columns where object_owner='ABC'; */

5.Check if Redaction is applied,

select card_no from payment_details ORDER BY id; //Card_no will become 0 if the redaction is applied.

6.Perform below operations,

Select concat(card_no,' ') from payment_details ;//This works fine

with table2 AS (
 Select concat(card_no,' ') from payment_details )
 select *  from table2;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error
 with table2 AS (
 Select sum(card_no) from payment_details )
 select *  from table2;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error
 with table2 AS (
 Select trim(card_no) from payment_details )
 select *  from table2;//This is giving 'ORA-28094: SQL construct not supported by data redaction' error




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

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