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
|
