Problem with temporary lobs

, ,
1

Hey

We are inserting an XML document into a clob field in the database (oracle 9.2.0.5) through a flow service. Because it is not directly supported (as far as I know, correct me if I’m wrong), we create a temporary clob, insert the document into it, then copy it to the destination field. After that the temporary clob is deleted. The creation and freeing the clob is done through a JDBC adapter.
The problem is that the temporary clob seems to last even after we explicitly delete it. It only vanishes after the time the JDBC session from our connection expires (we are using a webMethods connection). The problem is that we receive many request on this connection and thus the connection is still active. That way after some time the temporary tablespace is fully filled and normal SQL statements cannot be executed.
Did anyone had such a problem or has any idea/solution/workaround?

2

Hey,

It is essential to free CLOB after inserting it into database with
DBMS_LOB.createtemporary

when you wrap the service with a procedure or package method, please remember to add NOCOPY compiler hint with CLOB parameter. Like this :

PROCEDURE freeTemporaryClob(temporary IN OUT NOCOPY CLOB) IS
BEGIN
IF DBMS_LOB.istemporary(temporary) > 0 THEN
DBMS_LOB.freetemporary(temporary);
END IF;
END;

If you are using this procedure in a package, please make SURE, you declared it also with NOCOPY compiler hint. Otherwise CLOB will be copied and after that only one copy will be fried.

Use declaration like :
PROCEDURE freeTemporaryClob(temporary IN OUT NOCOPY CLOB);

We had such problem with one of our vendor’s code…

Best regards,

Andrzej Boczek
PTK Centertel
Poland