Skip to Main Content
  • Questions
  • View raise ORA-28094 based on "dbms_redact" base-table

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 10, 2020 - 10:02 am UTC

Answered by: Chris Saxon - Last updated: August 04, 2020 - 3:48 pm UTC

Category: PL/SQL - Version: 19.5; 18.3; 12.2

Viewed 100+ times

You Asked

Dear Ask-Tom-Team,

I've to apologize up front because I think I've something missed in the Documentation regarding dbms_redact setup, or restrictions therewith.
If so I'm sorry for wasting your time just to point me in right direction.

I've a simple test-case on livesql (but you cannot run it there, because you have no execute-right on dbms_redact), please have a look at:
https://livesql.oracle.com/apex/livesql/s/kcq634fgexodc6m6a8n4esb0l

My questions is regarding the ORA-28094 raised by the "tom_redact_view_28094"-View (at statement 13).
The restriction I couldn't find, and to be honest don't understand, is:
The above mentioned view just selects all columns from the base-table (which has one redacted column - ssn), and just add two columns together (nr1 + nr2). And this addition seems to be enough to raise the ORA-28094.
If you do not select the "sum" column from this view, it works just fine.

I've tested this on AIX and Oracle 19.5, and on Oracle 12.2 and 18.3 on Oracle-Linux.
All of the above shows the same behaviour, which is another reason why I think I've just missed a restriction in the documentation.
I know I could redact the column in the view, but I think this would defeat the purpose. Because I would like to redact the 'base-table' and rather not 10 views based on this table.

In the end my tests would go a step further, because my initial setup started with dbms_tsdp_* using dbms_redact. But I think that dbms_redact is the restricting part, so I've simplified this test-case.

I hope the test-case and my explanation is sufficient for you to reproduce the behaviour.
And as stated above if I've missed something in the documentation, and you can point me in the right direction, I'm grateful and sorry!

Thank you in advance for your time and help!
best regards,
Tom


with LiveSQL Test Case:

and we said...

I can reproduce this - thanks for the great test case!

I'm not sure why this is happening though; I can't find any documented restrictions either.

I recommend raising this with support. I suspect this is something that needs fixing.

and you rated our response

  (2 ratings)

Reviews

Thank you

July 14, 2020 - 7:19 am UTC

Reviewer: A reader

Dear Chris,

Thank you for your immediate response.
We'll follow your advice and open an SR.

regards,
Tom

Oracle-Support response

August 04, 2020 - 9:14 am UTC

Reviewer: Tom

Dear Chris,

Just for your information, in case the questions comes up again.
Here the Oracle-Support response:

What your are experiencing is NORMAL BEHAVIOR. Unfortunately there are limitation of what can be done when a redaction is used.
ORA-28094: SQL CONSTRUCT NOT SUPPORTED BY DATA REDACT ( Doc ID 2457158.1 )
The document just show a few examples. But if the ORA-28094 occurs then it is not supported when REDACTION is used.


regards,
Thomas

Chris Saxon

Followup  

August 04, 2020 - 3:48 pm UTC

Thanks for getting back to us.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.