Temporary LOBs in Oracle not freed up

Hi,

I'm not sure if it's a JDBC problem, but could be. I guess is more of an Oracle problem, but anyway somebody may have seen this.

The environment is Oracle 8.1.7.4 , Weblogic 7, oracle thin driver, Solaris.

I have a pl/sql package in Oracle, with a function which generates XML and returns
it in a CLOB variable. The function is called through a weblogic connection pool so
the session never closes. The CLOB in the pl/sql code is a temporary clob with session lifespan.

The problem is that the DB temporary tablespace is growing because one temp lob is allocated
every time when the function is called and it is not released. First I tried to allocate the temp clob in the pl/sql code every time when the function is called and free it when it is called next time (it is a package variable). Then I tried to create one for the session and reuse it. In both cases the temp clobs are piling up.

The temp space gets released (temp clobs disappear) only when the session is closed, so we
"bounce" the connection pool once a day. This closes and reopens all the connections.

Any pointer would be greatly appreciated.

Thank you,

Bela

***

Real-time disaster recovery
Visit http://WWW.EVERGREENASSURANCE.COM or call 410.571.6990 for information Received on Sun Apr 04 2004 - 14:04:38 CDT