ORA-00932: inconsistent datatypes: expected - got NCLOB

[pikhovkin]

Django 1.9.7
Oracle 12.1

Records in SILK_REQUEST table are exist.

ERROR:django.request:Internal Server Error: /silk/
Traceback (most recent call last):
  File "/.../local/lib/python2.7/site-packages/django/core/handlers/base.py", line 149, in get_response
    response = self.process_exception_by_middleware(e, request)
  File "/.../local/lib/python2.7/site-packages/django/core/handlers/base.py", line 147, in get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/.../local/lib/python2.7/site-packages/django/views/generic/base.py", line 68, in view
    return self.dispatch(request, *args, **kwargs)
  File "/.../local/lib/python2.7/site-packages/django/views/generic/base.py", line 88, in dispatch
    return handler(request, *args, **kwargs)
  File "/.../local/lib/python2.7/site-packages/django/utils/decorators.py", line 67, in _wrapper
    return bound_func(*args, **kwargs)
  File "/.../local/lib/python2.7/site-packages/django/utils/decorators.py", line 63, in bound_func
    return func.__get__(self, type(self))(*args2, **kwargs2)
  File "/.../local/lib/python2.7/site-packages/django/utils/decorators.py", line 67, in _wrapper
    return bound_func(*args, **kwargs)
  File "/.../local/lib/python2.7/site-packages/django/utils/decorators.py", line 63, in bound_func
    return func.__get__(self, type(self))(*args2, **kwargs2)
  File "/.../local/lib/python2.7/site-packages/silk/views/summary.py", line 83, in get
    c = self._create_context(request)
  File "/.../local/lib/python2.7/site-packages/silk/views/summary.py", line 73, in _create_context
    'most_time_spent_in_db': self._time_spent_in_db_by_view(filters),
  File "/.../local/lib/python2.7/site-packages/silk/views/summary.py", line 45, in _time_spent_in_db_by_view
    r = models.Request.objects.filter(view_name=view, *filters).annotate(t=Sum('queries__time_taken')).order_by('-t')[0]
  File "/.../local/lib/python2.7/site-packages/django/db/models/query.py", line 297, in __getitem__
    return list(qs)[0]
  File "/.../local/lib/python2.7/site-packages/django/db/models/query.py", line 258, in __iter__
    self._fetch_all()
  File "/.../local/lib/python2.7/site-packages/django/db/models/query.py", line 1074, in _fetch_all
    self._result_cache = list(self.iterator())
  File "/.../local/lib/python2.7/site-packages/django/db/models/query.py", line 52, in __iter__
    results = compiler.execute_sql()
  File "/.../local/lib/python2.7/site-packages/silk/sql.py", line 45, in execute_sql
    return self._execute_sql(*args, **kwargs)
  File "/.../local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 848, in execute_sql
    cursor.execute(sql, params)
  File "/.../local/lib/python2.7/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/.../local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/.../local/lib/python2.7/site-packages/django/db/utils.py", line 95, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/.../local/lib/python2.7/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/.../local/lib/python2.7/site-packages/django/db/backends/oracle/base.py", line 480, in execute
    return self.cursor.execute(query, self._param_generator(params))
DatabaseError: ORA-00932: inconsistent datatypes: expected - got NCLOB

[pikhovkin]

https://code.djangoproject.com/ticket/24096

[pikhovkin]

Added support of Oracle #258

[lingster]

Hi, I have had similar problems running this with Django 2.0.9 and Oracle 12.2.
I've identified the statement which causes the exception:
silk.views.summary.py:

    def _num_queries_by_view(self, filters):
        queryset = models.Request.objects.filter(*filters).values_list('view_name').annotate(t=Count('queries')).order_by('-t')[:5]
        views = [r[0] for r in queryset[:6]]
        requests = []
        for view in views:
            try:
                r = models.Request.objects.filter(view_name=view, *filters).annotate(t=Count('queries')).order_by('-t')[0]
                requests.append(r)
            except IndexError:
                pass
        return requests

The line
r = models.Request.objects.filter(view_name=view, *filters).annotate(t=Count('queries')).order_by('-t')[0]
causes the following sql to be generated where group by includes fields of type NCLOB, below a cut down query where sr.QUERY_PARAMS is an NCLOB field. A potential fix is to convert the NCLOB to a char with to_char() or to exclude the NCLOB fields.

SELECT to_char(sr.QUERY_PARAMS), count(ssq.id) FROM SILK_REQUEST sr
LEFT OUTER JOIN SILK_SQLQUERY ssq ON sr.id = ssq.REQUEST_ID
WHERE sr.VIEW_NAME = 'schema-swagger-ui'
GROUP BY to_char(sr.QUERY_PARAMS)

To do the exclusion the above statement could be replaced with:

r = models.Request.objects.filter(view_name=view, *filters).**values_list('id', 'queries')**.annotate(t=Count('queries')).order_by('-t')[0]
Or we change the models and replace TextField with CharField(), with a long enough character limit, but this might not be suitable for other dbms...

[pikhovkin]

@lingster you can try my fork of django-silk

[ori3t]

hi ,
Have similar issue with oracle db 19c and django 4.2.14
After install of latest silk ver , started django and when landing on the silk page I get this NCOB error. Added is the query from the dump file :

----- Error Stack Dump -----
<error barrier> at 0x7fffe0d5be00 placed dbkda.c@296
ORA-00932: inconsistent datatypes: expected - got NCLOB
----- Current SQL Statement for this session (sql_id=3x5p7h3fg9z9j) -----
SELECT "SILK_REQUEST"."ID", "SILK_REQUEST"."PATH", "SILK_REQUEST"."QUERY_PARAMS", "SILK_REQUEST"."RAW_BODY", "SILK_REQUEST"."BODY", "SILK_REQUEST"."METHOD", "SILK_REQUEST"."START_TIME", "SILK_REQUEST"."VIEW_NAME", "SILK_REQUEST"."END_TIME", "SILK_REQUEST"."TIME_TAKEN", "SILK_REQUEST"."ENCODED_HEADERS", "SILK_REQUEST"."META_TIME", "SILK_REQUEST"."META_NUM_QUERIES", "SILK_REQUEST"."META_TIME_SPENT_QUERIES", "SILK_REQUEST"."PYPROFILE", "SILK_REQUEST"."PROF_FILE", "SILK_REQUEST"."NUM_SQL_QUERIES", SUM("SILK_SQLQUERY"."TIME_TAKEN") AS "T" FROM "SILK_REQUEST" LEFT OUTER JOIN "SILK_SQLQUERY" ON ("SILK_REQUEST"."ID" = "SILK_SQLQUERY"."REQUEST_ID") WHERE "SILK_REQUEST"."VIEW_NAME" = :arg0 GROUP BY "SILK_REQUEST"."ID", "SILK_REQUEST"."PATH", "SILK_REQUEST"."QUERY_PARAMS", "SILK_REQUEST"."RAW_BODY", "SILK_REQUEST"."BODY", "SILK_REQUEST"."METHOD", "SILK_REQUEST"."START_TIME", "SILK_REQUEST"."VIEW_NAME", "SILK_REQUEST"."END_TIME", "SILK_REQUEST"."TIME_TAKEN", "SILK_REQUEST"."ENCODED_HEADERS", "SILK_REQUEST"."META_TIME", "SILK_REQUEST"."META_NUM_QUERIES", "SILK_REQUEST"."META_TIME_SPENT_QUERIES", "SILK_REQUEST"."PYPROFILE", "SILK_REQUEST"."PROF_FILE", "SILK_REQUEST"."NUM_SQL_QUERIES" HAVING SUM("SILK_SQLQUERY"."TIME_TAKEN") IS NOT NULL ORDER BY 18 DESC FETCH FIRST 1 ROWS ONLY