I'm using an Oracle 19 DB that is structured into multiple layers by using DB users. Here is a simplified overview of the structure:
- User DATA: Has the table
XTESTand the view
TEST. The view is created with a
CREATE VIEW TEST AS SELECT * FROM XTESTstatement, so it contains all columns of the table.
- User APP: Uses the view
DATA.TESTfor all SQL and DML.
The APP user does not have any grants on the table
DATA.XTEST, but does have SELECT,INSERT,UPDATE,DELETE grants on the view
Now to my problem:
If a NOT NULL constraint on the table
DATA.XTEST is violated, the error message doesn't show the column name. The error message is literally:
ORA-01400: cannot insert NULL into (???)
The DB used Oracle 11 some time ago and back then we had the same problem, but could fix it by granting the
REFERENCES privilege on the table
DATA.XTEST to the APP user (see related question: Grant privilege to see column names in null constraint violations when inserting in views). But this doesn't work any more in Oracle 19.
Does anyone know how to get the column name in the error message?