Re: Problem reading CLOB data from query result

Shai,

Thank you for the suggestion.  While I couldn't figure out what you were referring to on the LOB page of the documentation for cx_Oracle, "for result in cursor:" seems more 'pythonic' to me and also simpler so I've updated my code as you suggested.

I also poked at cursor.arraysize.  Setting arraysize to 1 allows me to get the data from the query without error.  I tried other values but hit the error at different record counts as follows:

cursor.arraysize	Max records read without error
-------------------		--------------------------------------
500			200
50			200
10			390
5			385
2			508

The only consistent pattern I'm seeing is that something is wrong in the way the cursor handles LOB data handles if arraysize is greater than 1.

With cursor.arraysize set to 1, I'm no longer blocked by this bug.  However, if there is additional debugging I can do to help resolve the underlying issue, please let me know so that I can provide additional data.

Thanks to everyone for the help and suggestions,

Andrew

-----Original Message-----
From: Shai Berger [mailto:shai-9mtU2oJkgntWk0Htik3J/[email protected]] 
Sent: Tuesday, December 15, 2015 18:01
To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
Subject: Re: [cx-oracle-users] Problem reading CLOB data from query result

On Tuesday 15 December 2015 23:04:51 Andrew Wheelwright wrote:
> 
> I have no idea what is magical about 200 in my environment.  I'm 
> guessing that I'm exhausting some resource related to our Oracle 
> server but I lack the experience to know what that might be or how to find that out.
> 

Actually, 200 sounds more like a client-side limit. The first suspect is cursor.arraysize, which is 50 by default but many set it differently. It is only supposed to affect performance, but you may have hit some corner that makes it affect more.

Also, I would replace:

	result = cursor.fetchone()
	while result is not None:
	   ...
	   result = cursor.fetchone() # <---- Invalid handle error thrown here

with 

	for result in cursor:
	   ...

just seems nicer, and fits better with
https://urldefense.proofpoint.com/v2/url?u=http-3A__cx-2Doracle.readthedocs.org_en_latest_lob.html&d=CwICAg&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=Y9Xz6SNdZPhFSPmEoxu-5YvaNMqw2XMguxLVQRFmXXw&s=FKGtfsuGd3UCHgy1GirhSS1S8fwU-HnfnJh04TYuQuI&e= 

HTH,
	Shai.

------------------------------------------------------------------------------
_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.sourceforge.net_lists_listinfo_cx-2Doracle-2Dusers&d=CwICAg&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=Y9Xz6SNdZPhFSPmEoxu-5YvaNMqw2XMguxLVQRFmXXw&s=yjEiirBXcXcRCGW9pVvJqOMEHIdHVmg-hhprAz9wQQM&e= 

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