Opened 6 years ago

Last modified 5 months ago

#11580 assigned Bug

Unable to query TextField against oracle nclob 10Gr4

Reported by: nosrednakram Owned by: shaib
Component: Database layer (models, ORM) Version: 1.6
Severity: Normal Keywords: oracle TextField
Cc: mboersma, 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

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

  1. response = callback(request, *callback_args, callback_kwargs)

File "/usr/lib/python2.4/site-packages/django/contrib/auth/decorators.py" in call

  1. return self.view_func(request, *args, kwargs)

File "/opt/django/eis/banobj/views.py" in search

  1. context_instance=RequestContext(request))

File "/usr/lib/python2.4/site-packages/django/shortcuts/init.py" in render_to_response

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

  1. return t.render(context_instance)

File "/usr/lib/python2.4/site-packages/django/template/init.py" in render

  1. return self.nodelist.render(context)

File "/usr/lib/python2.4/site-packages/django/template/init.py" in render

  1. bits.append(self.render_node(node, context))

File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node

  1. result = node.render(context)

File "/usr/lib/python2.4/site-packages/django/template/loader_tags.py" in render

  1. return compiled_parent.render(context)

File "/usr/lib/python2.4/site-packages/django/template/init.py" in render

  1. return self.nodelist.render(context)

File "/usr/lib/python2.4/site-packages/django/template/init.py" in render

  1. bits.append(self.render_node(node, context))

File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node

  1. result = node.render(context)

File "/usr/lib/python2.4/site-packages/django/template/loader_tags.py" in render

  1. result = self.nodelist.render(context)

File "/usr/lib/python2.4/site-packages/django/template/init.py" in render

  1. bits.append(self.render_node(node, context))

File "/usr/lib/python2.4/site-packages/django/template/debug.py" in render_node

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

Attachments (1)

11580.patch (979 bytes) - added by PyKaB 17 months ago.

Download all attachments as: .zip

Change History (13)

comment:1 Changed 6 years ago by ikelly

  • Component changed from Uncategorized to Database layer (models, ORM)
  • Keywords oracle TextField added
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from Unable to query TextFiled against oracle nclob 10Gr4 to Unable to query TextField against oracle nclob 10Gr4
  • Triage Stage changed from Unreviewed to Accepted

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 Changed 6 years ago by ikelly

  • Cc mboersma added

comment:3 Changed 6 years ago by nosrednakram

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 Changed 4 years ago by julien

  • Severity set to Normal
  • Type set to Bug

comment:5 follow-up: Changed 4 years ago by 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. 215 - 1).

comment:6 in reply to: ↑ 5 Changed 4 years ago by ikelly

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 Changed 4 years ago by 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.

comment:8 Changed 3 years ago by CarstenF

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

Changed 17 months ago by PyKaB

comment:9 Changed 17 months ago by PyKaB

  • Version changed from 1.1-beta to 1.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 Changed 7 months ago by susundberg

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 7 months ago by susundberg (previous) (diff)

comment:11 Changed 5 months ago by shaib

  • Owner changed from nobody to shaib
  • Status changed from new to assigned

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

comment:12 Changed 5 months ago by shaib

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.

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