how to manage CLOB type with cx_oracle

[Loredana]

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

 

[Loredana]

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?

thanks

Loredana

 

[Gabriel Genellina]

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."""

 

[Loredana]

En Tue, 03 Mar 2009 13:33:19 -0200, Loredana <[email protected]>  
escribió:

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.htmland 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."""

ok...
I try this code and it works:

curs.execute(sqlstr)
for rows in curs:
for col in rows:
try:
print col.read()
except:
print col


onother question?
which is the best wey (fastest way) to discern LOB from other type?

thanks

Loredana

 

[Gabriel Genellina]

I try this code and it works:

curs.execute(sqlstr)
for rows in curs:
for col in rows:
try:
print col.read()
except:
print col


onother question?
which is the best wey (fastest way) to discern LOB from other type?

type()? Add a line like this in the code above:
print type(col)