The Apex 18c and higher may conflict with Data Redaction.

Data Redaction

Mikhail Velikikh described a serious limitation in Data Redaction:

In a CREATE VIEW definition or an inline view, there cannot be any SQL expression that involves a redacted column.

I slightly modified Mikhail’s test case:

create table u.t1 ( first_name varchar2(30), last_name varchar2(30) ) ;

BEGIN
  DBMS_REDACT.add_policy(
    object_schema => 'u',
    object_name => 't1',
    column_name => 'first_name',
    policy_name => 'redact_t1',
    function_type => DBMS_REDACT.full,
    expression => '1=1'
  );
END;

BEGIN 
  DBMS_REDACT.alter_policy( 
    object_schema => 'u', 
    object_name => 't1', 
    column_name => 'last_name', 
    policy_name => 'redact_t1', 
    function_type => DBMS_REDACT.full 
  ); 
END;

The following queries, executed by another user, demonstrate what works and what doesn’t:

SQL> alter session set current_schema = u ;

Session altered.

SQL> select first_name || ' ' || last_name from t1 ;

no rows selected

SQL> select * from ( select first_name || ' ' || last_name from t1 ) ;
                                                           *
ERROR at line 1:
ORA-28094: SQL construct not supported by data redaction


SQL> select * from ( select first_name,  last_name from t1 ) ;

no rows selected

Simply put, the query breaks when it contains a SQL expression – in this case concatenation – on redacted columns in the inner query block (QB).

You can overcome this limitation by flipping the undocumented parameter “_strict_redaction_semantics” to “false”, but that’s the uncharted territory. Apart from Mikhail’s blog post, I couldn’t find any other document mentioning this parameter.

Apex

The following query works in SQLPlus but it doesn’t when we embed it in a Classic Report in Apex versions 18c or higher:

select first_name || ' ' || last_name from t1

It’s failing with:

ORA-28094: SQL construct not supported by data redaction

We can set the trace event to see what was executed:

alter system set events '28094 trace name errorstack forever, level 1';

This is what Apex actually sent:

select * from(select a.*,row_number() over (order by null) apx$rownum from(select i.*
 from (select "NAME"
from ((select /*+ qb_name(apex$inner) */d."NAME" from (select first_name || ' ' || last_name name from t1
) d
 )) i
) i where 1=1
order by "NAME" asc nulls last
)a
)where apx$rownum<=:p$_max_rows

Simply put, it embedded our original query in the inner query block (QB). The outer QB looks like it’s underpinning pagination.

Carsten Czarski informs us that pagination indeed underwent major improvements in 18c. Sadly, it also created a conflict with Data Redaction.

There was a shimmer of hope – the APEX$USE_NO_PAGINATION hint is supposed to revert the pagination to the APEX 5.1 behavior. As it turned out, the hint really removes the pagination, but, unfortunately, still places the original query in the inner QB:

select --+APEX$USE_NO_PAGINATION
* from (select i.*
 from (select "NAME"
from ((select /*+ qb_name(apex$inner) */d."NAME" from (select first_name || ' ' || last_name name from t1
) d
 )) i
) i where 1=1
order by "NAME" asc nulls last

So the old behavior isn’t fully emulated – Apex 5.1 doesn’t wrap the query, at least not for Classical Report:

select first_name || ' ' || last_name name from t1
 order by 1

Currently, it seems that our only option is to materialize calculated expressions in separate columns. In this example, we would need to store the expression “first_name || ‘ ‘ || last_name” in its own column “name”.

As I already mentioned, it’s also possible to prevent the Data Redaction restriction with the undocumented parameter, but that’s unsupported for now.