Unable to query TextField against oracle nclob 10Gr4

[Mark Anderson]

I have tried icontains and regex against a TextField on an oracle database and get the following error:

DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

The code was ported directly over from mysql where the i_contains query worked. The column was created as an NCLOB. I can query against other fields fine.

Environment:

Request Method: GET
Request URL: http://django/eis/banobj/search/?q=class
Django Version: 1.1 rc 1 SVN-11348
Python Version: 2.4.3
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.sites',
 'eis.banobj',
 'django.contrib.admin',
 'django.contrib.admindocs',
 'eis.ldapauth',
 'eis.emailLogs']
Installed Middleware:
('django.middleware.common.CommonMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware')

Template error:
In template /opt/django/eis/templates/banobj/arealist.html, error at line 17
   Caught an exception while rendering: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

   7 : {% block content %}
   8 : <h1> {{heading}} </h1>
   9 : <table border=1>
   10 : <tr>
   11 :    <th>Banner Object</th>
   12 :    <th>Ready For Testing</th>
   13 :    <th>Tested</th>
   14 :    <th>Primary User</th>
   15 :    <th>Object Type</th>
   16 : </tr>
   17 :  {% for obj in  obj_list %} 
   18 : <tr bgcolor="{% cycle rowcolors %}">
   19 :    <td><a href="/eis/banobj/wiki/{{ obj.id }}/">{{ obj.name|upper }}</a></td>
   20 :    <td>{% if obj.prod_svn %}
   21 :           <img src="/media/img/admin/icon-yes.gif">
   22 :        {% else %}
   23 :        <img src="/media/img/admin/icon-no.gif">
   24 :        {% endif %}</td>
   25 :    <td>{% if obj.user_tested %}
   26 :           <img src="/media/img/admin/icon-yes.gif">
   27 :        {% else %}


Traceback:
File "/usr/lib/python2.4/site-packages/django/core/handlers/base.py" in get_response
  92.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python2.4/site-packages/django/contrib/auth/decorators.py" in __call__
  78.             return self.view_func(request, *args, **kwargs)
File "/opt/django/eis/banobj/views.py" in search
  172.                               context_instance=RequestContext(request))
File "/usr/lib/python2.4/site-packages/django/shortcuts/__init__.py" in render_to_response
  20.     return HttpResponse(loader.render_to_string(*args, **kwargs), **httpresponse_kwargs)
File "/usr/lib/python2.4/site-packages/django/template/loader.py" in render_to_string
  108.     return t.render(context_instance)
File "/usr/lib/python2.4/site-packages/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/usr/lib/python2.4/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/lib/python2.4/site-packages/django/template/loader_tags.py" in render
  97.         return compiled_parent.render(context)
File "/usr/lib/python2.4/site-packages/django/template/__init__.py" in render
  178.         return self.nodelist.render(context)
File "/usr/lib/python2.4/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node
  71.             result = node.render(context)
File "/usr/lib/python2.4/site-packages/django/template/loader_tags.py" in render
  24.         result = self.nodelist.render(context)
File "/usr/lib/python2.4/site-packages/django/template/__init__.py" in render
  779.                 bits.append(self.render_node(node, context))
File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node
  81.             raise wrapped

Exception Type: TemplateSyntaxError at /banobj/search/
Exception Value: Caught an exception while rendering: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


Original Traceback (most recent call last):
  File "/usr/lib/python2.4/site-packages/django/template/debug.py", line 71, in render_node
    result = node.render(context)
  File "/usr/lib/python2.4/site-packages/django/template/defaulttags.py", line 126, in render
    len_values = len(values)
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 81, in __len__
    self._result_cache = list(self.iterator())
  File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 238, in iterator
    for row in self.query.results_iter():
  File "/usr/lib/python2.4/site-packages/django/db/models/sql/query.py", line 287, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/lib/python2.4/site-packages/django/db/models/sql/query.py", line 2383, in <lambda>
    result = iter((lambda: cursor.fetchmany(GET_ITERATOR_CHUNK_SIZE)),
  File "/usr/lib/python2.4/site-packages/django/db/backends/oracle/base.py", line 469, in fetchmany
    return tuple([self._rowfactory(r)
DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 

[Erin Kelly]

This looks like it may be the same error: ​http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b7950ba7df01b90e

The apparent solution would be to substr out the first 1000 characters of the lob rather than the first 4000 characters like we're doing now. Or better, remove the SUBSTR hack altogether and get TextField lookups working using DBMS_LOB.INSTR instead.

[Mark Anderson]

Hello,

Thanks for your quick reply, I did a rough hack based on your information, I'll try and get to cleaning up at a later date so I can search the entire lob contents and maybe even post a patch. This will get me by until I can come back to this in a week or so unless someone else creates a patch first. This also allowed the searching of lobs within the ADMIN interface again.

I modified django/db/backends/oracle/base.py and gave a size to the substr, it didn't have one, FYI.

WAS:

    def field_cast_sql(self, db_type):
        if db_type and db_type.endswith('LOB'):
            return "DBMS_LOB.SUBSTR(%s)"
        else:
            return "%s"

NOW:

    def field_cast_sql(self, db_type):
        if db_type and db_type.endswith('LOB'):
            return "DBMS_LOB.SUBSTR(%s,2000,1)"
        else:
            return "%s"

[morgy.wahl@…]

I have the same problem here. In tests (which may be dependent on my data), I didn't get the error for substring lengths of 2000 or less, but did for 2001 and greater. I'm not the DBA so I'm not sure how CLOBs are encoded, but UTF-16 with a 4000-byte limit would make sense, since all the text I'm storing is in the ASCII set (and thus would be 2 bytes per character in UTF-16). I'm not sure what Oracle version I'm using. (how can I find out?)

I tried removing the call to SUBSTR all together, and it worked fine (at least, with icontains).

FYI the Oracle docs seem to indicate DBMS_LOB.SUBSTR defaults to an offset of 1 and a length of 32767 (i.e. 2¹⁵ - 1).

[Erin Kelly]

Replying to morgy.wahl@…:

I have the same problem here. In tests (which may be dependent on my data), I didn't get the error for substring lengths of 2000 or less, but did for 2001 and greater. I'm not the DBA so I'm not sure how CLOBs are encoded, but UTF-16 with a 4000-byte limit would make sense, since all the text I'm storing is in the ASCII set (and thus would be 2 bytes per character in UTF-16). I'm not sure what Oracle version I'm using. (how can I find out?)

This query will show you your database character set (CLOBs) and your national character set (NCLOBs):

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

This query will show you the database version:

SELECT * FROM V$VERSION;

I tried removing the call to SUBSTR all together, and it worked fine (at least, with icontains).

Interesting, according to the docs it appears that LIKE is actually supported for CLOBs, although = and <> are not. That will be very useful in fixing this.

[morgy.wahl@…]

Thanks. Those return:

NLS_CHARACTERSET WE8MSWIN1252

NLS_NCHAR_CHARACTERSET AL16UTF16

and:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

So it seems my NCLOBs are indeed UTF-16, which confirms the 4000-byte limit. And, on Oracle 11g at least, just removing the call to DBMS_LOB.SUBSTR works.

[PyKaB]

It's no need to use function DBMS_LOB.SUBSTR in oracle 11g or greater.
On the other hand, this function does not guarantee work for Oracle version below 11g for the use of several clobs.
Perhaps it makes sense to abandon support for Oracle versions below 11g in Django 1.6 or 1.7.

[Pauli Sundberg]

Hey all, i had the same problem. I am using django-1.4 (for customer reason) and "Oracle Database 11g Release 11.2.0.4.0 - 64bit Production".

Using the PyKAB patch (remove DBMS_LOB.SUBSTR ) did remove the problem with count() call, but it introduces new bug that causes that query .. exclude( my_textfield = "" ) not work properly: also the ones that have "" content are returned.

EDIT: Actually there is something else also wrong, either my understanding or 'exclude' operator:

In [20]: statistics_data.models.Measurement.objects.all().values_list( 'json_values' )
Out[20]: [(u'foo bar',), (u'foo bar',), (u'',)]

In [21]: statistics_data.models.Measurement.objects.filter(json_values="").count()
Out[21]: 1

In [22]: statistics_data.models.Measurement.objects.exclude(json_values="").count()
Out[22]: 3

EDIT 2: But this seems to work:

In [24]: statistics_data.models.Measurement.objects.exclude(json_values__isnull=True).count()
Out[24]: 2

EDIT 3: See https://code.djangoproject.com/ticket/21001 -- that is Bug in 1.4. that is fixed in master, but not backported.

[Shai Berger]

Well, I can reproduce this on master... needs fixing.

[Shai Berger]

Partial ​PR fixes the contains lookup, up for review of the method of installing backend-specific custom lookups.

Note that the code uses DBMS_LOB.INSTR -- the preferable way to do this is using CONTAINS, but that requires the text-field to be indexed.

Just removing the DBMS_LOB.SUBSTR as suggested works with the LIKE queries, but breaks the exact ones.

[Mariusz Felisiak]

I couldn't reproduce a crash on Oracle 12c+ and Django 2.0+. Unfortunately, I'm not sure where (Django/Oracle/cx_Oracle) and when it was fixed, ​PR

[Mariusz Felisiak]

Replying to Mariusz Felisiak:

I couldn't reproduce a crash on Oracle 12c+ and Django 2.0+. Unfortunately, I'm not sure where (Django/Oracle/cx_Oracle) and when it was fixed, ​PR

CI confirmed that it's still an issue.

[Shai Berger]

Two points:

Replying to Mariusz Felisiak:

Replying to Mariusz Felisiak:

​PR

IIUC, the PR discussion seems to include a workaround (a setting that makes the error go away). It may be worthwhile to include it in ​the oracle notes.

CI confirmed that it's still an issue.

My old ​PR includes a technical solution to the problem. It was put on hold all these years ago, because I tried to do something additional in it: Define a general way for database backends to override a built-in lookup, without blatant monkeypatching. With that, the Oracle solution was done in a way which would have been available to a 3rd-party database backend.

It may or may not be worthwhile to revive that idea, but either way, we can also take the technical Oracle solution that's given there, and apply it "the old way"; I hope it still works.