SQL expressions can be used to build reports based on columns that have Oracle Data Redaction policies defined on them.

1. Connect to the PDB as a user who has the EXECUTE privilege on the DBMS_REDACT PL/SQL package and the ADMINISTER REDACTION POLICY system or schema privilege.
2. Create the following Data Redaction policy for the HR.EMPLOYEES table.
   This policy will replace the first 4 digits of the value from the SALARY column with the number 9 and the first digit of the value from the COMMISSION_PCT column with a 9.

   BEGIN
    DBMS_REDACT.ADD_POLICY(
      object_schema          => 'HR', 
      object_name            => 'EMPLOYEES', 
      column_name            => 'SALARY',
      column_description     => 'Employees salary column shows employee salary',
      policy_name            => 'redact_emp_sal_comm', 
      policy_description     => 'Partially redacts the employee salary column',
      function_type          => DBMS_REDACT.PARTIAL,
      function_parameters    => '9,1,4',
      expression             => '1=1');
   END;
   /
   BEGIN
    DBMS_REDACT.ALTER_POLICY(
      object_schema          => 'HR',
      object_name            => 'EMPLOYEES',
      policy_name            => 'redact_emp_sal_comm',
      action                 => DBMS_REDACT.ADD_COLUMN,
      column_name            => 'COMMISSION_PCT',
      function_type          => DBMS_REDACT.PARTIAL,
      function_parameters    => '9,1,1',
      expression             => '1=1');
   END;
   /
   

3. Connect as the HR user and then run the following report.
   This report will use the SQL expression (SALARY + COMMISSION_PCT) to combine the employees' salaries and commissions.

   SELECT (SALARY + COMMISSION_PCT) total_emp_compensation
   FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID = 80;
   
   TOTAL_EMP_COMPENSATION
   ----------------------
                   0
                   0
                   0
   ...
   

4. Use SQL expressions for the report, including concatenation.
   For example:

   SELECT 'Employee ID '          || EMPLOYEE_ID ||
          ' has a salary of '     || SALARY || 
          ' and a commission of ' || COMMISSION_PCT || '.' detailed_emp_compensation
   FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID = 80
   ORDER BY EMPLOYEE_ID;
   
   DETAILED_EMP_COMPENSATION
   -------------------------------------------------------------
   Employee ID 150 has a salary of 99990 and a commission of .95.
   Employee ID 151 has a salary of 9999 and a commission of .95.
   Employee ID 152 has a salary of 9999 and a commission of .9.
   ...

5. Connect the user who created the redact_emp_sal_comm Data Redaction policy.
6. Run the following statement to drop the policy.

   BEGIN
     DBMS_REDACT.DROP_POLICY (
       object_schema => 'HR',
       object_name   => 'EMPLOYEES',
       policy_name   => 'redact_emp_sal_comm');
   END;
   /