I've found the following:
To insert a text using the cx_Oracle driver directly if you do something
like
import cx_Oracle
connection = cx_Oracle.connect('connectionstring')
cursor = connection.cursor()
p = ''
for i in range(1,10000):
p += str(i)
sss = "INSERT INTO
ipvpn_route_table(read_datetime,ip_vpn,route_table,destinations) VALUES
(NULL,6,'" + p + "','" + p + "')"
cursor.execute(sss)
It fails with the same error as the web2py traceback
DatabaseError: ORA-01704: string literal too long
The correct way to do it is by passing it with parameters (and it works with
any ammount of characters below 4GB of clob data) as follows:
import cx_Oracle
connection = cx_Oracle.connect('... connection string')
cursor = connection.cursor()
p = ''
for i in range(1,10000):
p += str(i)
sss = "INSERT INTO
ipvpn_route_table(read_datetime,ip_vpn,route_table,destinations) VALUES
(NULL,6,:FOO,:BAR)"
cursor.execute(sss, FOO=p, BAR=p)
El lunes, 3 de agosto de 2015, 9:51:16 (UTC-6), Boris Aramis Aguilar
Rodríguez escribió:
>
> Hi,
>
> I've been currently working with Oracle as a database backend, I have
> found one issue that I highly suspect has to do with the DAL Adapter; when
> you use web2py text fields they get mapped into CLOB with oracle database
> backend (as you can see on the OracleAdapter code) the issue is dealing
> with text longer than 4k characters. Oracle doesn't support a typical
> insert with more than 4k characters dealing with a CLOB data type, because
> it treats it as a string and strings can't be more than 4k characters long.
> So for example dealing with a table like
>
> db.define_table('atable', Field('longtext', 'text'))
>
> db.atable.insert(longtext=somelongtext) #This fails with the following
> exception
>
> File "/var/www/web2py/gluon/packages/dal/pydal/objects.py", line 691, in
> insert
> ret = self._db._adapter.insert(self, self._listify(fields))
> File "/var/www/web2py/gluon/packages/dal/pydal/adapters/base.py", line 731,
> in insert
> raise e
> DatabaseError: ORA-01704: string literal too long
>
> And reading trough several forums I found that this error appears on CLOB
> insertion when you do a tipycal insert.
> https://community.oracle.com/thread/1068414?start=0&tstart=0
>
> So i guess OracleAdapter is trying to achieve a typical insertion when it
> shouldn't as it has to deal with CLOB logic.
>
> I think I could fix the problem but I'm not really sure if someone can help
> me out with a hint of some kind.
>
> Thanks!
>
>
>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
