Hi

I'm experiencing problems when freeing a temporary TLobLocator at certain times.

My TOracleQuery calls a stored proc which has a CLOB parameter for saving data. The stored proc then calls another stored proc and passes the CLOB parameter through.

It would appear that when the parameter in the second stored proc is declared as a CLOB, an exception is generated in Delphi when I come to free the temporary TLobLocator.

If the second stored proc uses a long parameter, there is no problem. However, we really need it to be a CLOB because of the amount of data we are sending.

Can you please help?

(Using DOA 4.0.3)

Many thanks
Chris

What exactly is the exception message?

It's very strange. There is an exception, it is captured by the exception handler, however there is NO message. (i.e. E.message="")

1. Have downloaded DOA 4.0.5 - Same problem occurs.

2. Although exception message is blank, if I use EOracleError the exception numbers raised are:-
32550540, 33070844, 32554684, 32553312
If I "unload" my delphi app from IIS and try again I get the same sequence of error numbers(!)

These numbers don't seem right. What is the Exception class (EOracleError or something else)?

Here is my code:

try
myCLOB.Free;
except
on E: EOracleError do
begin
LogAction('Save Dealer Exception - Unable to free Temporary CLOB'+#13#10+
'mess='+e.Message+' num='+FloatToStr(E.ErrorCode));
end;
end;

and here is an extract from my log file:

29/06/2004 10:42:57 Save Dealer Exception - Unable to free Temporary CLOB
mess= num=32550540
29/06/2004 10:42:58 Save Dealer Exception - Unable to free Temporary CLOB
mess= num=33070844
29/06/2004 10:43:17 Save Dealer Exception - Unable to free Temporary CLOB
mess= num=32554684
29/06/2004 10:43:18 Save Dealer Exception - Unable to free Temporary CLOB
mess= num=32553312

If the exception was not an EOracleError surely this handler will not capture the exception(?)

It is indeed an EOracleError. The bogus error code makes me wonder if the session is still connected at this time. Can you verify this?

Yes. The TOracleQuery.Session.Connected property is still true at this point.

Okay. Can you send me a little demo project that reproduces the issue?

Demo project and stored procs sent as requested.

To summarise: Delphi would appear to be unable to free the temporary LOB, only when all the following are true:-
1) OracleQuery calls a stored proc which calls another
2) Both stored procs have a CLOB input parameter
3) Both stored procs evaluate the CLOB as XML

I eagerly await your comments.

Hi Marco. Hope you received my demo project ok.

Any ideas?

Thanks
Christopher

I did receive it, but have not yet been able to reproduce the problem. The program runs successfully. I will try it on some other configurations.

Can you let me know your client and server version?

Hello Marco, thanks for the feedback.

I'm using Delphi 5 with DOA 4.0.5
Oracle server version 9.2.0.4.0
Oracle client version 9.2.0.1.0
All on Windows NT 4 platform.

Okay, I will try a similar configuration.

Hi Marco. Any update?

No luck yet.

Marco
Can you let me know what configuration you used that works okay?
This may help us.
Regards
Christopher

I have successfully tested it on a 9.0 and 10g server, but I have been able to reproduce it on a 9.2.0.4 server. I actually get an ORA-22922 error (nonexistent LOB value). This happens when the LOB Locator is freed. The only workaround I can think of at the moment is to ignore the error in TLOBLocator.Free.

Thanks Marco.

We hope to upgrade to 9.2.0.5 soon. Have you tried that version?

If it is specific to certain Oracle versions, maybe that will solve it.

However, if it is an Oracle bug, I am unsure how to raise this with Oracle, as I cannot see a way to reproduce the error without using DOA.

I am currently using that work around. However, our application is an IIS app, and it appears to be leaking memory. This problem seems to be prime candidate.

Any further suggestions welcome.

Regards
Christopher

I have not tried 9.2.0.5, but I will give that a try too.

The error will most likely cause a memory leak, since the OCI handle for the LOB Locator is not freed.

Marco

I've now tried against a 9.2.0.5 server, and the problem is still present.
We are still having to unload our IIS application at regular intervals to combat the memory leak.

Any further suggestions welcome.

Regards
Christopher

Marco

Has there been any update on this?

Regards
Christopher

Unfortunately not. Freeing the handle on 9.2 causes an exception, not freeing it causes a memory leak. We have not been able to find a workaround (other than downgrading or upgrading Oracle).

Marco

If this is an Oracle Bug, can you provide details of the bug so we can chase this matter with Oracle please?

Many thanks
Christopher

I cannot find a specific bug that matches this problem. There are more than 50 Oralce bugs logged against temporary LOB's though.