Re: CLOB updates using ADODB not showing in trace file

On Mar 7, 1:17 pm, SR <s..._at_goawaynms-sys-ltd.demon.co.uk> wrote:
> Oracle 8i database on Solaris 9. I know 8i is old, but given that (in our case) Oracle are happy to
> take the money and provide extended support, then I feel perfectly within rights to post a question
> here, so please, try none of the tiresome replies on getting upgraded.
>
> SQL_TRACE is set to TRUE at the database level.
>
> Initially trace was being enabled via a logon database trigger, but since we noticed the problem I
> am about to describe we also switched it on at database level to be sure.
>
> The problem is that updates to CLOB columns on the database in a VB app are not showing up in the
> trace files. Granted, the "Updates" are being performed via the ADODB AppendChunk method (and not
> via direct SQL), but I would have thought that the ADODB API would have to present pucka SQL (or
> PLSQL) to Oracle. The columns value is being updated - we can see it change. Either this method is
> somehow bypassing the SQL layer (how likely is that?), or trace gets turned off for the duration of
> the "update". Just to add to the confusion, the SQL statements before and after the CLOB "update"
> are present.
>
> We have noticed no difference between enabling trace via "alter session set sql_trace=true" and the
> event 10046 command (cant remember exact syntax).
>
> MTS is not enabled - server connection is dedicated. Even so, we have searched all the trace files
> in udump in case there was some separation of the trace info into different files occurring.
>
> Anyone seen this?

You can post questions here even if you do not have a support contract.

My guess is that since LOB data is not buffered like normal table/ index blocks that the normal trace process does not see the LOB update. There may be an event you can set to get the trace information but I do not know it and a quick scan of the message files did not turn up any useful information.

I suspect that you are out of luck but someone else may know more.

HTH -- Mark D Powell -- Received on Wed Mar 07 2007 - 14:07:48 CST