ProgrammingError: LOB variable no longer valid after subsequent fetch

[DJ]

Hi,

I get this error while retrieving CLOB data types from Oracle. This is
also seen with the Web2Py admin interface when it tries to display all
data rows. I am using the cx_oracle python database module.

From their website,
http://cx-oracle.sourceforge.net/html/lob.html
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.

It appears that Web2py DAL is using the fetchall method to retrieve
the data. Kindly advise if you have any suggestions/ideas to fix this.

Thanks,
Sebastian

[mdipierro]

Which python version? I vaguely remember this had been addressed and
the problem has gone away.

Massimo

On Aug 28, 2:40 pm, DJ <sebastianjaya...@gmail.com> wrote:
> Hi,
>
> I get this error while retrieving CLOB data types from Oracle. This is
> also seen with the Web2Py admin interface when it tries to display all
> data rows. I am using the cx_oracle python database module.
>

> From their website,http://cx-oracle.sourceforge.net/html/lob.html

[DJ]

Massimo,

I am using -

Python 2.5.2
DB Driver: cx_Oracle-5.0.1-10g.win32-py2.5
Oracle 10G
Platform: Windows 2003 Server

-Sebastian

On Aug 29, 4:22 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Which python version? I vaguely remember this had been addressed and
> the problem has gone away.
>
> Massimo
>

[mdipierro]

OK. I know the problem. The fact is web2py does not create CLOBs so it
cannot handle them.

Actually this problem arises also with BLOBs and we fixed it long ago.
The same fix would apply to CLOBs too but there is no way for web2py
to know that a field is a CLOB since we do not provide a way to define
it.

I imagine you have a model that treats a legacy CLOB as text, correct?

[DJ]

Hi Massimo,

Yes, you are correct. I used the "TEXT" datatype for an application I
am building. While it works on MySQL, it throws errors on Oracle.

Web2Py by default creates CLOB datatypes on Oracle when you specify
TEXT in the model which causes this error. How did you fix this issue
with BLOBs?

I eventually had to do a work-around. I modified the Oracle datatype
from CLOB to VARCHAR2 with a large character limit and changed the
model definition from "text" to "string".

-Sebastian

On Sep 1, 8:42 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> OK. I know the problem. The fact is web2py does not create CLOBs so it
> cannot handle them.
>
> Actually this problem arises also with BLOBs and we fixed it long ago.
> The same fix would apply to CLOBs too but there is no way for web2py
> to know that a field is a CLOB since we do not provide a way to define
> it.
>
> I imagine you have a model that treats a legacy CLOB as text, correct?
>

[mdipierro]

Try this. In gluon.sql.py (in trunk) after line 400 add

if fieldtype == 'text' and dbname ==
'oracle':
return ":CLOB('%s')" % obj.replace('\'','\'\'')

[Jonathan Calderon]

Massimo,

I am using -

Python 3.6
DB Driver:cx-Oracle==5.3

Oracle 11g and 12G
Platform: Windows 7 Ultimate

[Dave S]

You're seeing this error with current web2py? (The message you're responding to is 8 years old.)

Web2py isn't officially able to run with Py3, although they've been working on it and it's Real Close Now.  Are you using the latest nightly build, or some other version?

/dps
 

[Massimo Di Pierro]

I would not swear by web2py on python 3 with cx-oracle. Nobody here has tested that. If you get to the bottom of it, please let us know.