Bug on DAL OracleAdapter

[Boris Aramis Aguilar Rodríguez]

Steps to reproduce:

db.define_table('atable', Field('longtext', 'text'))#this makes longtext to be a clob in oracle database
for i in range(1, 100):
  db.atable.insert(longtext=str(i))

rows = db(db.atable.id>0).select()
for r in rows:
  print r.longtext #this fails with the following exception

Traceback (most recent call last):
  File "<console>", line 1, in <module>
ProgrammingError: LOB variable no longer valid after subsequent fetch

As I was trying to fix the error I found that LOB variables if not called .read() they can't be accessed afterwards, so this only happened when you selected something that returned more than 1 row. I finally found the issue in OracleAdapter method _fetchall

def _fetchall(self):
        if any(x[1]==cx_Oracle.LOB for x in self.cursor.description):
            return [tuple([(c.read() if type(c) == cx_Oracle.LOB else c) \
                               for c in r]) for r in self.cursor]
        else:
            return self.cursor.fetchall()

So this method calls the read() method when it detects that the data is cx_Oracle.LOB data type... but as long as the latest version of cx_Oracle CLOB is not the same as LOB so this code fails for CLOB data (text fields in web2py dal and Oracle backend).

So, finally the bugfix is to detect for CLOB and LOB data types and call the read() method

The BugFix

def _fetchall(self):
        if any(x[1]==cx_Oracle.LOB or x[1]==cx_Oracle.CLOB for x in self.cursor.description):
            return [tuple([(c.read() if type(c) == cx_Oracle.LOB else c) \
                               for c in r]) for r in self.cursor]
        else:
            return self.cursor.fetchall()

[Massimo Di Pierro]

In trunk! :-)