how to manage CLOB type with cx_oracle

Gabriel Genellina gagsl-py2 at yahoo.com.ar
Tue Mar 3 11:12:36 EST 2009


En Tue, 03 Mar 2009 13:33:19 -0200, Loredana <loredana.pier at gmail.com>  
escribió:

> On Mar 3, 1:01 pm, Loredana <loredana.p... at gmail.com> wrote:
>> Hi,
>>
>> I need to read CLOB field type (it is long text)
>>
>> if I use this code:
>>
>> curs.execute(sqlstr)
>> rows['name_of_columns']     =   name_of_columns
>> rows['data']                         =   curs.fetchall()
>>
>> it returns me this values:
>>
>> test = {'name_of_columns': ['FILENAME', 'CRONTIME', 'SHORT_TAIL',
>> 'LONG_TAIL'], 'data': [('dd','asdds','adadsa',<cx_Oracle.LOB object at
>> 0x2a955bc230>')]}
>>
>> any ideas?
>>
>> Thanks
>>
>> Lory
>
> Hi all,
> I success to read one row with the following code:
>
>         curs.execute(sqlstr)
>         name_of_columns =   []
>         for fieldDesc in curs.description:
>             name_of_columns.append(fieldDesc[0])
>         for rows_ in curs.fetchone():
>             try:
>                 print rows_.read()
>             except:
>                 print "except. ",rows_
>
> but if I try with fetchmany() it doesn't work
> any ideas?

cx_Oracle implements DBAPI 2.0, then you should follow the general  
guidelines in the specification:
http://www.python.org/dev/peps/pep-0249/

LOBs are an extension to DBAPI -- see  
http://cx-oracle.sourceforge.net/html/lob.html and carefully read the  
second note:

"""Note: 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. In particular,  
do not use the fetchall() method. The exception “LOB variable no longer  
valid after subsequent fetch” will be raised if an attempt to access a LOB  
variable after a subsequent fetch is detected."""

-- 
Gabriel Genellina




More information about the Python-list mailing list