Problem with the UnicodeText type that stores data in an Oracle NCLOB column

[Pierre Bossé]

Hi everyone,

We have a problem with the UnicodeText type that stores data in an
Oracle NCLOB column.
When we use a unicode string in python to write data in the table, we
are limited to 4000 characters while it is possible to store a lot
more characteres (6000 in the following example) using a str type.

Is there something we are doing wrong?

Thanks for your help.

Here is a small program that produce the error we get:

#! /usr/bin/env python
# -*- coding: latin-1 -*-

"""
Definition from the WEB
An NCLOB is a character large object containing Unicode characters,
with a maximum size of 128 terabytes
for Oracle 11g and 4 gigabytes for the earlier releases. The text data
in an NClOB field can sit in a
separate tablespace from the table that references it. Like the CLOB
datatype, the NCLOB datatype has
full transactional support so that changes made to an NCLOB
participate fully in transactions.
Manipulations of NCLOB value can be committed and rolled back.
"""

from elixir import *

class SysTags(Entity):
using_options(tablename='x')

id = Field(Integer, primary_key=True, colname='ID')
data = Field(UnicodeText, nullable=False, colname='DATA')

metadata.bind = 'oracle://ychoquet:dev@refvec_dev' # Oracle Database
10g Release 10.2.0.3.0 - 64bit Production
metadata.bind.echo = True

setup_all()
drop_all()
create_all()

SysTags(id=1, data='àâçûùéêàëô'*600) # with a str type, lengt=6000 >
4000
session.commit()
record = SysTags.query.filter_by(id=1).one()
print '(%d) length = %d - %s' % (record.id, len(record.data),
record.data[0:10])

SysTags(id=2, data=u'àâçûùéêàëô'*400) # with a unicode type, length =
4000
session.commit() # it works but this is the limit!
record = SysTags.query.filter_by(id=2).one()
print '(%d) length = %d - %s' % (record.id, len(record.data),
record.data[0:10])

SysTags(id=3, data=u'àâçûùéêàëô'*400+u'à') # with a unicode type,
length = 4001
session.commit() # Here, we have an exception «ValueError: unicode
data too large»
record = SysTags.query.filter_by(id=3).one()
print '(%d) length = %d - %s' % (record.id, len(record.data),
record.data[0:10])


Here is the output from the console:

pydev debugger: starting
2012-04-04 13:34:42,533 INFO sqlalchemy.engine.base.Engine SELECT USER
FROM DUAL
2012-04-04 13:34:42,533 INFO sqlalchemy.engine.base.Engine {}
2012-04-04 13:34:42,549 INFO sqlalchemy.engine.base.Engine SELECT
table_name FROM all_tables WHERE table_name = :name AND owner
= :schema_name
2012-04-04 13:34:42,549 INFO sqlalchemy.engine.base.Engine {'name':
u'X', 'schema_name': u'YCHOQUET'}
2012-04-04 13:34:42,565 INFO sqlalchemy.engine.base.Engine
DROP TABLE x
2012-04-04 13:34:42,565 INFO sqlalchemy.engine.base.Engine {}
2012-04-04 13:34:42,595 INFO sqlalchemy.engine.base.Engine COMMIT
2012-04-04 13:34:42,595 INFO sqlalchemy.engine.base.Engine SELECT
table_name FROM all_tables WHERE table_name = :name AND owner
= :schema_name
2012-04-04 13:34:42,595 INFO sqlalchemy.engine.base.Engine {'name':
u'X', 'schema_name': u'YCHOQUET'}
2012-04-04 13:34:42,611 INFO sqlalchemy.engine.base.Engine
CREATE TABLE x (
"ID" INTEGER NOT NULL,
"DATA" NCLOB NOT NULL,
PRIMARY KEY ("ID")
)


2012-04-04 13:34:42,611 INFO sqlalchemy.engine.base.Engine {}
2012-04-04 13:34:42,706 INFO sqlalchemy.engine.base.Engine COMMIT
C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-py2.7.egg
\sqlalchemy\engine\default.py:465: SAWarning: Unicode type received
non-unicode bind param value.
processors[key](compiled_params[key])
2012-04-04 13:34:42,720 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2012-04-04 13:34:42,720 INFO sqlalchemy.engine.base.Engine INSERT INTO
x ("ID", "DATA") VALUES (:ID, :DATA)
2012-04-04 13:34:42,720 INFO sqlalchemy.engine.base.Engine {'DATA':
'\xe0\xe2\xe7\xfb\xf9\xe9\xea\xe0\xeb\xf4 <--(repeat this string 600
times)',
'ID': 1}
2012-04-04 13:34:42,753 INFO sqlalchemy.engine.base.Engine COMMIT
2012-04-04 13:34:42,767 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2012-04-04 13:34:42,767 INFO sqlalchemy.engine.base.Engine SELECT
x."ID" AS "x_ID", x."DATA" AS "x_DATA"
FROM x
WHERE x."ID" = :ID_1
2012-04-04 13:34:42,767 INFO sqlalchemy.engine.base.Engine {'ID_1': 1}

(1) length = 6000 - àâçûùéêàëô


2012-04-04 13:34:42,799 INFO sqlalchemy.engine.base.Engine INSERT INTO
x ("ID", "DATA") VALUES (:ID, :DATA)
2012-04-04 13:34:42,799 INFO sqlalchemy.engine.base.Engine {'DATA':
u'\xe0\xe2\xe7\xfb\xf9\xe9\xea\xe0\xeb\xf4 <--(repeat this string 400
times)',
'ID': 2}
2012-04-04 13:34:42,799 INFO sqlalchemy.engine.base.Engine COMMIT
2012-04-04 13:34:42,815 INFO sqlalchemy.engine.base.Engine BEGIN
(implicit)
2012-04-04 13:34:42,815 INFO sqlalchemy.engine.base.Engine SELECT
x."ID" AS "x_ID", x."DATA" AS "x_DATA"
FROM x
WHERE x."ID" = :ID_1
2012-04-04 13:34:42,815 INFO sqlalchemy.engine.base.Engine {'ID_1': 2}

(2) length = 4000 - àâçûùéêàëô


2012-04-04 13:34:42,831 INFO sqlalchemy.engine.base.Engine INSERT INTO
x ("ID", "DATA") VALUES (:ID, :DATA)
2012-04-04 13:34:42,831 INFO sqlalchemy.engine.base.Engine {'DATA':
u'\xe0\xe2\xe7\xfb\xf9\xe9\xea\xe0\xeb\xf4 <--(repeat this string 400
times and add byte \xe0)',
'ID': 3}
2012-04-04 13:34:42,831 INFO sqlalchemy.engine.base.Engine ROLLBACK

Traceback (most recent call last):
File "E:\eclipse\plugins
\org.python.pydev.debug_2.4.0.2012020116\pysrc\pydevd.py", line 1307,
in <module>
debugger.run(setup['file'], None, None)
File "E:\eclipse\plugins
\org.python.pydev.debug_2.4.0.2012020116\pysrc\pydevd.py", line 1060,
in run
pydev_imports.execfile(file, globals, locals) #execute the script
File "E:\Workspace\test\test\test.py", line 36, in <module>
session.commit() # Here, we have an exception «ValueError: unicode
data too large»
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\scoping.py", line 113, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\session.py", line 645, in commit
self.transaction.commit()
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\session.py", line 313, in commit
self._prepare_impl()
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\session.py", line 297, in _prepare_impl
self.session.flush()
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\session.py", line 1547, in flush
self._flush(objects)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\session.py", line 1616, in _flush
flush_context.execute()
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\unitofwork.py", line 328, in execute
rec.execute(self)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\unitofwork.py", line 472, in execute
uow
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\orm\mapper.py", line 2193, in _save_obj
execute(statement, multiparams)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\engine\base.py", line 1399, in execute
params)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\engine\base.py", line 1532, in
_execute_clauseelement
compiled_sql, distilled_params
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\engine\base.py", line 1633, in _execute_context
context)
File "C:\Program Files\Python27\lib\site-packages\sqlalchemy-0.7.3-
py2.7.egg\sqlalchemy\engine\default.py", line 330, in do_execute
cursor.execute(statement, parameters)
ValueError: unicode data too large