Re: Dynamically Add Error Logging To DML? VPD?

How about a trigger on AFTER SERVERERROR? I've done this in the past, using the trigger to dump info on the session and statement into a table that I can review at a later time.

Dave

On Tue, Sep 12, 2017 at 4:04 PM, Jack Applewhite < jack.applewhite_at_austinisd.org> wrote:

> 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.
>
>
> 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
> Unlimited)
> 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 <(512)%20414-9250> (wk)
>
> 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, including attachments.
>

--
http://www.freelists.org/webpage/oracle-l