[cx-oracle-users] cx_Oracle LOB handling

[Anthony T. <an...@co...>]

In the past couple of weeks I have had three people complain about the 
following behavior with respect to LOB variables:

Imagine a table called "CLOBS" with a column "CLOBVAL" which has 25 rows 
in it and the programmer desires to get the values of the column to 
manipulate it in some way. The straightforward approach is to do the 
following:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
cursor.execute("select clobval from clobs")
for value, in cursor.fetchall():
     print "Doing some manipulation with value", value.read()

Unfortunately, the data returned will not match what is actually in the 
table. In the above case where the arraysize is unchanged from the 
default value of 1, the LOB value will always be equal to the last value 
actually fetched. This is not expected and results in a bug report to 
me. :-)

The fact of the matter, however, is that it does make sense if you 
understand what is going on "underneath the covers" so to speak. Oracle 
uses LOB locators to allow you to access large objects and these must be 
allocated prior to any fetches. It would be unwise to allocate one of 
these "locators" for each row retrieved due to space and time 
considerations. However, it would be nice if this fact was made a little 
more obvious. :-)

The proper code is as follows:

import cx_Oracle

connection = cx_Oracle.connect("user/pw@tns")
cursor = connection.cursor()
cursor.execute("select clobval from clobs")
for value, in cursor:
     print "Doing some manipulation with value", value.read()

This code works because the rows are fetched one at a time from the 
iterator.

I am suggesting that the following changes be made:

The documentation have the following lines added to the section on LOB 
variables:

Internally, Oracle uses LOB locators which are allocated based
on the cursor array size. Thus, it is important that the data in the LOB 
object be manipulated before another internal fetch takes place. The 
safest way to do this is to use the cursor as an iterator.

The code be modified to raise a ProgrammingError when this situation is 
detected with the following text:

"LOB variable no longer valid after subsequent fetch"

Please comment!

Anthony