Re: How to capture SQL_TEXT of queries?

Dragon Fly wrote:
>
> In short, I need to set up kind of a trigger
> on some of the tables so that it captures the
> text of the queries run against those tables.
> The problem is, the "standard"
> triggers can be set up only for UPDATE OR INSERT
> while I need a trigger for SELECT statement.
> Anybody has an idea what is the good way of implementing
> such kind of thing?
>
> Thanks,
> SergeiIt is possible to put a trigger on the AUD$ table using a known Oracle
workaround (Log 678775).
If you enable auditing of the particular tables/query statements, the AUD$ trigger will fire whenever a user performs the appropriate query.

In the trigger, you can retrieve information from the v$session table to give you a last_sql_address (of the current sql being executed by a particular session). You can then link to v$sqlarea or v$sqltext to retrieve the actual sql and log it.

NOTE:- You can only do this in 7.3 or above since earlier versions of v$session do NOT have a last_sql_address as well as sql_address. The problem with earlier versions is that when your AUD$ trigger fires, the current sql_address points to the trigger query you are executing so you lose the actual user query.

This is quite an involved method but can work quite well. The only warning is that the AUD$ trigger can become unstable when used on user queries. I currently use it as an on-login trigger and it's fine but I have had occasional problems with using it as a 'select' trigger.

Jez. Received on Fri Apr 11 1997 - 00:00:00 CDT