Aw: Dynamically Add Error Logging To DML? VPD?
Date: Wed, 13 Sep 2017 22:10:08 +0200
Message-ID: <trinity-100aa941-3333-4fe5-b830-6d91cd7c7119-1505333408495_at_3c-app-webde-bs38>
Von: "Jack Applewhite" <jack.applewhite_at_austinisd.org>
An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Betreff: 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 the predicate.
' 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 this 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:
ORA-00907: missing right parenthesis
Anyone know of a trick to get around this or other method to dynamically add the "Log Errors..." line?
Thanks.
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)
...you drove yourself to 5th grade.
...both you and your wife work without a shirt.