Oracle database TextField limitations and Django admin interface queries
A little background: my application is used to manage observational data which is collected from designated routes annually. Each route object has a set of observation events which in turn have a date field. The data is managed by using a (terrible) customized Django admin interface. In the admin interface list view it's possible to search routes using a custom search box which just injects parameters like ?route_no=1234 and so forth into the url resulting in a filtered list view. For the most part this works but when I want to search for routes that, for example, have observation events past certain date with a query like ?observationevent__observation_date__gte=2010-01-01 (supposed to show all routes that have been observed this decade) it raises an DatabaseError: ORA-00932: inconsistent datatypes: expected - got NCLOB-exception while using an Oracle database in production. The query seems to work when using a development SQLite database or doing a query like Route.objects.filter(observationevent__observation_date__gte='2012-01-01') directly in the management console even when using the Oracle production database. According to the general notes on databases the Oracle backend has limitations with TextField-related queries ( https://docs.djangoproject.com/en/dev/ref/databases/#textfield-limitations) and this seems to be causing this as far as I can tell. As the queries are created by Django admin I have no idea how I should proceed from here. Is there some kind of easy way to prevent this from happening on Oracle or should I implement some kind of manual search function? I am currently using the following versions: Django==1.4.5 cx-Oracle==5.1.2 gunicorn==0.17.2 virtualenv==1.5.1 My apologies in advance if this has been answered before or I am not posting this in a proper way as this is my first post here. :) -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at http://groups.google.com/group/django-users?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: Oracle database TextField limitations and Django admin interface queries
Thank you for the reply,
The field is defined in the Django model as:
location_description = models.TextField(_('additional route information'),
blank=True)
Data length for the corresponding column in the database seems to be 4000
and the type is NCLOB. The database is created by Django's syncdb and is
not a legacy one.
This is the raw SQL query the Django admin interface produces:
SELECT DISTINCT "app_route"."id",
"app_route"."route_no",
"app_route"."owner_no",
"app_route"."municipality_code",
"app_route"."location_description",
"app_route"."latitude",
"app_route"."longitude"
FROM "app_route"
inner join "app_observationevent"
ON ( "app_route"."id" =
"app_observationevent"."route_id" )
WHERE "app_observationevent"."observation_date" >= :arg0
ORDER BY "app_route"."id" DESC
Executing it via the Django management shell django.db.connection produces
the same error (ORA-00932: inconsistent datatypes: expected - got NCLOB)
but when I remove the "app_route"."location_description" field from the
SELECT projection the query executes successfully.
On Wednesday, April 10, 2013 4:09:43 PM UTC+3, Jani Tiainen wrote:
>
> 10.4.2013 14:45, Lauri Savolainen kirjoitti:
> > A little background: my application is used to manage observational data
> > which is collected from designated routes annually. Each route object
> > has a set of observation events which in turn have a date field. The
> > data is managed by using a (terrible) customized Django admin interface.
> >
> > In the admin interface list view it's possible to search routes using a
> > custom search box which just injects parameters like ?route_no=1234 and
> > so forth into the url resulting in a filtered list view. For the most
> > part this works but when I want to search for routes that, for example,
> > have observation events past certain date with a query like
> > ?observationevent__observation_date__gte=2010-01-01 (supposed to show
> > all routes that have been observed this decade) it raises an
> > DatabaseError: ORA-00932: inconsistent datatypes: expected - got
> > NCLOB-exception while using an Oracle database in production. The query
> > seems to work when using a development SQLite database or doing a query
> > like
> >
> Route.objects.filter(observationevent__observation_date__gte='2012-01-01')
> > directly in the management console even when using the Oracle production
> > database.
> >
> > According to the general notes on databases the Oracle backend has
> > limitations with TextField-related queries
> > (
> https://docs.djangoproject.com/en/dev/ref/databases/#textfield-limitations)
>
> > and this seems to be causing this as far as I can tell. As the queries
> > are created by Django admin I have no idea how I should proceed from
> > here. Is there some kind of easy way to prevent this from happening on
> > Oracle or should I implement some kind of manual search function?
> >
> > I am currently using the following versions:
> > Django==1.4.5
> > cx-Oracle==5.1.2
> > gunicorn==0.17.2
> > virtualenv==1.5.1
> >
>
> Welcome to among us very few and brave to use Oracle... ;)
>
> What comes to your problem few details are missing but:
>
> Length of the field in model definition and what column type is in the
> database. Also it would be helpful to know are you using legacy database?
>
> Actual error is slightly problematic since it doesn't directly tell what
> is wrong. It's related types of bind variables and any of them might be
> incorrect for some reason.
>
> --
> Jani Tiainen
>
> - Well planned is half done and a half done has been sufficient before...
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: Oracle database TextField limitations and Django admin interface queries
On Saturday, April 13, 2013 7:54:43 PM UTC+3, Ian wrote:
>
> On Saturday, April 13, 2013 10:40:10 AM UTC-6, Ian wrote:
>>
>> On Friday, April 12, 2013 7:43:32 AM UTC-6, Lauri Savolainen wrote:
>>>
>>> Thank you for the reply,
>>>
>>> The field is defined in the Django model as:
>>> location_description = models.TextField(_('additional route
>>> information'), blank=True)
>>>
>>> Data length for the corresponding column in the database seems to be
>>> 4000 and the type is NCLOB. The database is created by Django's syncdb and
>>> is not a legacy one.
>>>
>>> This is the raw SQL query the Django admin interface produces:
>>>
>>> SELECT DISTINCT "app_route"."id",
>>> "app_route"."route_no",
>>> "app_route"."owner_no",
>>> "app_route"."municipality_code",
>>> "app_route"."location_description",
>>> "app_route"."latitude",
>>> "app_route"."longitude"
>>> FROM "app_route"
>>>inner join "app_observationevent"
>>>ON ( "app_route"."id" =
>>> "app_observationevent"."route_id" )
>>> WHERE "app_observationevent"."observation_date" >= :arg0
>>> ORDER BY "app_route"."id" DESC
>>>
>>> Executing it via the Django management shell django.db.connection
>>> produces the same error (ORA-00932: inconsistent datatypes: expected - got
>>> NCLOB) but when I remove the "app_route"."location_description" field
>>> from the SELECT projection the query executes successfully.
>>>
>>>
>> Yes, this is caused by the use of a SELECT DISTINCT over a LOB column,
>> which Oracle does not allow. The DISTINCT was added to admin queries over
>> joined fields as a result of ticket #15819. As for workarounds -- I'm
>> afraid I don't have any suggestions for you.
>>
>
> Actually, one suggestion: you could try removing the field from the
> list_display option on the ModelAdmin. I think you would also need to
> override the get_queryset method on the ModelAdmin to .defer() the field,
> as it doesn't look like the admin would do that automatically.
>
Hello,
Neither approach seemed to work so I ended up changing the field to a
fixed-length type in the model and resyncing my tables which seems to work.
Anyways, thank you for the suggestions.
- Lauri Savolainen
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
