Opened 15 years ago

Last modified 2 years ago

#11580 new Bug

Unable to query TextField against oracle nclob 10Gr4

Reported by: Mark Anderson Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: oracle TextField
Cc: Matt Boersma, carsten.fuchs@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Mariusz Felisiak)

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 

Attachments (1)

11580.patch (979 bytes ) - added by PyKaB 10 years ago.

Download all attachments as: .zip

Change History (18)

comment:1 by Erin Kelly, 15 years ago

Component: UncategorizedDatabase layer (models, ORM)
Keywords: oracle TextField added
Summary: Unable to query TextFiled against oracle nclob 10Gr4Unable to query TextField against oracle nclob 10Gr4
Triage Stage: UnreviewedAccepted

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.

comment:2 by Erin Kelly, 15 years ago

Cc: Matt Boersma added

comment:3 by Mark Anderson, 15 years ago

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"

comment:4 by Julien Phalip, 13 years ago

Severity: Normal
Type: Bug

comment:5 by morgy.wahl@…, 13 years ago

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. 215 - 1).

in reply to:  5 comment:6 by Erin Kelly, 13 years ago

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.

comment:7 by morgy.wahl@…, 13 years ago

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.

comment:8 by Carsten Fuchs, 12 years ago

Cc: carsten.fuchs@… added
Easy pickings: unset
UI/UX: unset

by PyKaB, 10 years ago

Attachment: 11580.patch added

comment:9 by PyKaB, 10 years ago

Version: 1.1-beta1.6

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.

comment:10 by Pauli Sundberg, 10 years ago

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.

Last edited 10 years ago by Pauli Sundberg (previous) (diff)

comment:11 by Shai Berger, 9 years ago

Owner: changed from nobody to Shai Berger
Status: newassigned

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

comment:12 by Shai Berger, 9 years ago

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.

comment:13 by Mariusz Felisiak, 4 years ago

Version: 1.6master

comment:14 by Mariusz Felisiak, 2 years ago

Description: modified (diff)

comment:15 by Mariusz Felisiak, 2 years ago

Owner: Shai Berger removed
Status: assignednew

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

in reply to:  15 ; comment:16 by Mariusz Felisiak, 2 years ago

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.

in reply to:  16 comment:17 by Shai Berger, 2 years ago

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.

Note: See TracTickets for help on using tickets.
Back to Top