Oracle Data Redaction General Usage Limitations

It is important to understand usage limitations for using Oracle Data Redaction.

• Do not include any redacted columns in the following expressions or functions that are used in a GROUP BY clause and SELECT list in a SQL statement.

  • JSON operators

  • XML functions

  • SQL expression in a cursor expression

  • Constructor functions

  • User defined operators

  • PL/SQL functions

  Oracle does not support this, and raises an ORA-00979: not a GROUP BY expression error. This happens because the expressions or functions in the SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the GROUP BY clause (which is not updated by Data Redaction) leading to this unintended error message.

• Do not include any redacted columns in the following SQL expressions or functions that are used in both the DISTINCT clause and ORDER BY clause in a SQL statement.

  • XML functions

  • Cursor expression

  • Constructor functions

  • User defined operators

  • PL/SQL functions

  Oracle does not support this, and raises an error: ORA-01791: not a SELECTed expression. This happens because internally the expression in the SELECT list must be modified by Data Redaction, but this causes it to no longer be found when it comes time to process the ORDER BY clause, leading to this unintended error message.

• Oracle does not support adding a Data Redaction policy on a virtual column:

  While Oracle supports having a Data Redaction policy on the base column of a virtual column, you cannot apply a Data Redaction policy to the virtual column itself.

  SELECT COLUMN_ID, COLUMN_NAME, VIRTUAL_COLUMN
  FROM DBA_TAB_COLS
  WHERE TABLE_NAME = 'table_name_that_you_attempted_to_redact'
  AND VIRTUAL_COLUMN = 'YES'
  ORDER BY COLUMN_ID;