Why not start with
AUDIT INSERT ON xxx WHENEVER NOT SUCCESSFUL;
AUDIT UPDATE ON xxx WHENEVER NOT SUCCESSFUL;
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jack Applewhite
Sent: Tuesday, September 12, 2017 5:05 PM
Subject: Dynamically Add Error Logging To DML? VPD?
I use DBMS_ErrLog to create logging tables a LOT and really like this feature
for troubleshooting SQL. It works only if you can add the "Log Errors ..." line
at the end of your DML statement. We have a COTS application that generates DML
but is violating various table constraints. We can't nail down the SQL or the
rows it's trying to affect. We can't edit the DML to add the "Log Errors..."
line, so I was trying to figure out a way to do it dynamically.
Just now I tried using VPD to add a predicate to any DML on a table and
sneakily tack on the "Log Errors..." line. The policy function returns this for
' 1=1 Log Errors Into <ErrLogTable> Reject Limit Unlimited '
I thought that, since the "Log Errors..." line is always tacked on the end of
the SQL, this might work. The function compiled and I created the policy, both
in Sys. When I ran an Insert that I knew would violate a table constraint I got
SQL Error: ORA-28113: policy predicate has error
28113. 00000 - "policy predicate has error"
*Cause: Policy function generates invalid predicate.
*Action: Review the trace file for detailed error information.
I found the trace file in bdump and saw that VPD adds the Where, then encloses
the predicate text in parentheses:
Insert .... WHERE (1 = 1 Log Errors Into <ErrLogTable> Reject Limit
ORA-00907: missing right parenthesis
Anyone know of a trick to get around this or other method to dynamically add
the "Log Errors..." line?
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
You might be a Redneck if ...
...turning on any light in your house involves pulling a string.
...you drove yourself to 5th grade.
...both you and your wife work without a shirt.
-- Jeff Foxworthy
Confidentiality Notice: This email message, including all attachments, is for
the sole use of the intended recipient(s) and may contain confidential student
and/or employee information. Unauthorized use of disclosure is prohibited under
the federal Family Educational Rights & Privacy Act (20 U.S.C. §1232g, 34 CFR
Part 99, 19 TAC 247.2, Gov't Code 552.023, Educ. Code 21.355, 29 CFR
1630.14(b)(c)). If you are not the intended recipient, you may not use,
disclose, copy or disseminate this information. Please call the sender
immediately or reply by email and destroy all copies of the original message,