[cx-oracle-users] Problem reading CLOB data from query result

[Andrew W. <and...@fa...>]

I'm new to cx_Oracle and am in the process of creating a python script to pull about 4,000 records from an oracle database for my company.  The query involved uses xmlagg to create two CLOB fields.  I'm able to successfully fetch records one at a time and read the CLOB data for the first 200 records of the query result but when I try to fetch the 201st record I get an Invalid Handle error from cx_Oracle the context for which is "LobVar_PreFetch(): free temporary LOB" (I don't know whether that's even useful information).

My code uses the following logic:

connection = cx_Oracle.connect('schema/password@server')
cursor = connection.cursor()
cursor.execute('my query')
records = []
result = cursor.fetchone()
while result is not None:
    record = {}
    for index, field in enumerate(result): # convert the result tuple to a dictionary object
        if field is not None and cursor.description[index][1] == cx_Oracle.CLOB:
            field = field.read()  # get the CLOB value
        record[cursor.description[index][0]] = field
    records.append(record)
    result = cursor.fetchone() # <---- Invalid handle error thrown here

I suspect the problem I'm hitting is probably due to me going about handling large objects the wrong way so I appreciate any suggestions or recommendations on better ways to do read CLOB data.

I'm running python 3.5 on 64 bit Windows 7

Thank you for your help,

Andrew Wheelwright