Re: Problem reading CLOB data from query result

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:[email protected]] 
Sent: Monday, December 14, 2015 23:04
To: cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
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-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=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-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=CwIF-g&c=z0adcvxXWKG6LAMN6dVEqQ&r=r8n22QhFUAPJDR_VlIDWddq2o_hv2gm-i73xXR1k6jfSqv6lYXybbcyOCn6zKs-3&m=sON8EPilu2DBwG8x01K94bQxjapowKtykSN7Y_yo6IA&s=lHdCJMR7JpgVxukfRkVzrRaOMllvjJVgN4m2YmZ4bkg&e= 

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