Re: [Django] #11580: Unable to query TextField against oracle nclob 10Gr4

Django

unread,

Dec 15, 2011, 12:57:24 PM12/15/11

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: nosrednakram | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.1-beta-1
Severity: Normal | Resolution:
Keywords: oracle TextField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by CarstenF):

* cc: carsten.fuchs@… (added)
* ui_ux: => 0
* easy: => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:8>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Nov 8, 2013, 2:55:13 AM11/8/13

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: nosrednakram | Owner: nobody
Type: Bug | Status: new

* version: 1.1-beta => 1.6

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:9>

Django

unread,

Sep 2, 2014, 3:57:32 AM9/2/14

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: nosrednakram | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.6
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle TextField | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (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.

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:10>

Django

unread,

Nov 15, 2014, 11:02:56 AM11/15/14

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------

Reporter: nosrednakram | Owner: shaib
Type: Bug | Status: assigned

Changes (by shaib):

* owner: nobody => shaib
* status: new => assigned

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:11>

Django

unread,

Nov 15, 2014, 2:30:55 PM11/15/14

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: nosrednakram | Owner: shaib
Type: Bug | Status: assigned
Component: Database layer | Version: 1.6
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: oracle TextField | Needs documentation: 0
Has patch: 0 | Patch needs improvement: 0
Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by shaib):

Partial [https://github.com/django/django/pull/3544 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:12>

Django

unread,

Dec 10, 2019, 7:19:35 AM12/10/19

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------

Severity: Normal | Resolution:
Keywords: oracle TextField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by felixxm):

* version: 1.6 => master

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:13>

Django

unread,

Nov 18, 2021, 4:07:00 AM11/18/21

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: Mark Anderson | Owner: Shai
| Berger
Type: Bug | Status: assigned

Component: Database layer | Version: dev

Description changed by Mariusz Felisiak:

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

New 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
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
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:14>

Django

unread,

Nov 18, 2021, 4:43:39 AM11/18/21

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------

Reporter: Mark Anderson | Owner: (none)
Type: Bug | Status: new

Changes (by Mariusz Felisiak):

* owner: Shai Berger => (none)
* status: assigned => new

Comment:

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, [https://github.com/django/django/pull/15100 PR]

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:15>

Django

unread,

Nov 18, 2021, 7:23:53 AM11/18/21

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: Mark Anderson | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle TextField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Mariusz Felisiak):

Replying to [comment:15 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, [https://github.com/django/django/pull/15100 PR]

CI confirmed that it's still an issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:16>

Django

unread,

Nov 20, 2021, 10:14:27 AM11/20/21

to django-...@googlegroups.com

#11580: Unable to query TextField against oracle nclob 10Gr4
-------------------------------------+-------------------------------------
Reporter: Mark Anderson | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle TextField | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Shai Berger):

Two points:

Replying to [comment:16 Mariusz Felisiak]:

> Replying to [comment:15 Mariusz Felisiak]:

> > [https://github.com/django/django/pull/15100 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
[https://docs.djangoproject.com/en/3.2/ref/databases/#id14 the oracle
notes].

> CI confirmed that it's still an issue.

My old [https://github.com/django/django/pull/3544 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/11580#comment:17>

Reply all

Reply to author

Forward