I have an oracle view that was recently recreated, and we're seeing that the select statement has a literal value ('**********') for one column when we view the DDL for the view in SqlDeveloper, but that does not match the DDL that was executed. Because of this, when querying the view, that column is always returning the literal, never the actual value.
As a simple example:
CREATE OR REPLACE FORCE VIEW order_list
(
order_id,
customer_acct_nbr
acct_name,
order_date
)
AS
SELECT b.order_id,
a.customer_acct_nbr,
a.order_date,
FROM acct a,
orders b
WHERE a.acct_nbr = b.acct_nbr;
Becomes:
CREATE OR REPLACE FORCE VIEW order_list
(
order_id,
customer_acct_nbr
acct_name,
order_date
)
AS
SELECT b.order_id,
'*********' customer_acct_nbr ,
a.order_date,
FROM acct a,
orders b
WHERE a.acct_nbr = b.acct_nbr;
What I think is happening, but can't find any documentation to confirm it is:
1.) I know that the user that executed the DDL has masking applied to certain columns on the underlying table ( we are using Dynamic data masking ). 2.) the data for that column will always be retuned as '**********' when the query is run by the creating user, so when the view is created, the statement is optimized to substitute that literal value in the SQL for the view.
I have verified that this is what is happening by executing the DDL as both a user who has masking applied and one who does not: in the case of having masking applied, I see the literal in the resulting DDL, in the case of the one who does not have masking applied I see the actual sql I executed and get the unamsked values in the view.
Is this a known behavior of Oracle views? (We are using Oracle 19)