How to log bind variables on trigger after servererror

Question

I need to track the value of bind variables to a query that returns an Oracle error.

Since the queries that return errors are not drawn in v$sql_bind_capture and v$sqlarea, I can't look at these tables.

Then I created a trigger after Servererror and logged the query in a trace table created by me. Unfortunately the bind variables are not traced (I read :p0, :myVar, etc...).

How can I do to track the value of these variables if I have not access to the session ?

score 0 · Answer 1 · answered Oct 13 '14 at 08:39

0

You can trace the session and set the LEVEL to capture bind variable values.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 4';

The different LEVELs of tracing event:-

0 – No trace. Like switching sql_trace off.

2 – The equivalent of regular sql_trace.

4 – The same as 2, but with the addition of bind variable values.

8 – The same as 2, but with the addition of wait events.

12 – The same as 2, but with both bind variable values and wait events.

Have a look at How to generate trace file – SQL Trace and TKPROF in Oracle

answered Oct 13 '14 at 08:39

Lalit Kumar B

47,486
13
97
124

Unfortunately I do not have access to the session – antferr Oct 13 '14 at 08:45
You can ask your DBA or someone who could do this. Tracing the session would give you exact information. – Lalit Kumar B Oct 13 '14 at 09:03

How to log bind variables on trigger after servererror

1 Answers1