[cx-oracle-users] Freeing Temporary BLOBs

[Henning v. B. <H.v...@t-...>]

Hello LOB experts,

how can I free temporary LOBs before the session ends?

I have a stored function that returns a temporary LOB.
I can successfully get the content from Python,
but when I am not able to free the memory (on the DB server)
before the session ends.

Here is an example:

SQL:

create or replace function test_blob (pi_size in binary_integer)
return blob
is
  v_blob blob;
begin
  dbms_lob.createtemporary (v_blob, true, DBMS_LOB.SESSION);
  dbms_lob.write (v_blob, 1, 1, utl_raw.cast_to_raw(rpad('AB', pi_size,
'X')));
  return v_blob;
end test_blob;
/

---------------------

Python

import cx_Oracle
import sys

import logging
logging.basicConfig(level=3Dlogging.INFO,
                    format=3D'%(asctime)s %(levelname)s %(message)s'
                   )
log =3D logging.getLogger()

conn =3D cx_Oracle.connect ("someuser/password@ORCL")
log.info ("connected")

curs =3D conn.cursor()
blob =3D curs.callfunc ("test_blob", cx_Oracle.BLOB, (200,))
log.info ("FNC returned")
content =3D blob.read()
log.info ("BLOB data read")
log.info ("data: %r", content)

curs.execute ("select * from v$temporary_lobs");
rset =3D curs.fetchall()
log.info ("v$temporary_lobs=3D\n%r", rset)

# THIS DOES NOT WORK, raises cx_Oracle.NotSupportedError:
#curs.setinputsizes(blob=3Dcx_Oracle.BLOB)  # does not help
#curs.execute ("begin DBMS_LOB.FREETEMPORARY (:blob); end;", =
blob=3Dblob)

del blob

curs.execute ("select * from v$temporary_lobs");
rset =3D curs.fetchall()
log.info ("v$temporary_lobs=3D\n%r", rset)

curs.close()
conn.close()

You can see that even after deleting the blob on the python side,
v$temporary_lobs still shows one LOB.

I think OCILobFreeTemporary would be the right function here,
but it seems not to be exposed to the python module.

How to workaround this?

Henning