cx-oracle-users

[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

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

[Christopher J. <chr...@or...>]

What's magic about 200 in your environment?

What size LOBs? Do you really need to instantiate them all at once in records?

What version of cx_Oracle?

On 9/12/2015 9:10 am, Andrew Wheelwright wrote:
> 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 201^st 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
>
>
>
> ------------------------------------------------------------------------------
>
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

-- 
http://twitter.com/ghrd

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

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

Christopher,

I'm using cx_Oracle 5.2.  I also neglected to mention that I'm running 64 bit python.

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.

The CLOBs are created by the query using xmlagg to aggregate some text fields in a related table into a single value for the row.  Consequently, the CLOBs aren't a fixed size (they range from 5 to 241 from the rows I'm able to pull; I assume that's in bytes but the cx_Oracle docs don't actually specify the unit).

If you have any suggestions as to how I could debug/investigate this further, I would appreciate it.

Cheers,

Andrew

-----Original Message-----
From: Christopher Jones [mailto:chr...@or...] 
Sent: Monday, December 14, 2015 23:04
To: cx-...@li...
Subject: Re: [cx-oracle-users] Problem reading CLOB data from query result

What's magic about 200 in your environment?

What size LOBs? Do you really need to instantiate them all at once in records?

What version of cx_Oracle?

On 9/12/2015 9:10 am, Andrew Wheelwright wrote:
> 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 
> 201^st 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
>
>
>
> ----------------------------------------------------------------------
> --------
>
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.sourceforge
> .net_lists_listinfo_cx-2Doracle-2Dusers&d=CwIF-g&c=z0adcvxXWKG6LAMN6dV
> EqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3
> &m=sON8EPilu2DBwG8x01K94bQxjapowKtykSN7Y_yo6IA&s=lHdCJMR7JpgVxukfRkVzr
> RaOMllvjJVgN4m2YmZ4bkg&e=
>

--
https://urldefense.proofpoint.com/v2/url?u=http-3A__twitter.com_ghrd&d=CwIF-g&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=sON8EPilu2DBwG8x01K94bQxjapowKtykSN7Y_yo6IA&s=XdAQvQbJc9pCldl6o9TJbi4_mEdgxkPJR9NxzxY5MDw&e= 

------------------------------------------------------------------------------
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.sourceforge.net_lists_listinfo_cx-2Doracle-2Dusers&d=CwIF-g&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=sON8EPilu2DBwG8x01K94bQxjapowKtykSN7Y_yo6IA&s=lHdCJMR7JpgVxukfRkVzrRaOMllvjJVgN4m2YmZ4bkg&e= 

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

[Shai B. <sh...@pl...>]

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 
http://cx-oracle.readthedocs.org/en/latest/lob.html

HTH,
	Shai.

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

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

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:sh...@pl...] 
Sent: Tuesday, December 15, 2015 18:01
To: cx-...@li...
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-...@li...
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=