Django ORM getting cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended

I've got class in models:

class Region(models.Model):
    region_id=models.AutoField(primary_key=True,db_column="region_id")
    region_name=models.CharField(max_length=4000,blank=True,db_column="region_name")
    def __str__(self):
        return f"region_id={self.region_id}, region_name={self.region_name}"
    class Meta:
        db_table = "regions"

Now I'm trying to query database from shell:

python manage.py shell

s=Region.objects.all()
s
When I'm connected to PostgreSQL everything is fine, but when I connect to Oracle (has same table) I'm getting "cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended" doing the same as with PostgreSQL. Is there some bug in cx_Oracle or what?

[cjbj]

Do some tracing of the SQL statement via DPI_DEBUG_LEVEL https://cx-oracle.readthedocs.io/en/latest/user_guide/tracing_sql.html#low-level-sql-tracing-in-cx-oracle and see what SQL is being generated. Then work out what should be generated. Maybe Django needs to be changed.

[stale]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale]

This issue has been automatically closed because it has not been updated for a month.

[kishorpawar]

Hi @cjbj ,

I figured out what my SQL query is. The query runs in psql, dbeaver. It only fails when running django.

In my scenario, I am trying to connect the remote oracle database. I have installed clients and added the path to LD_LIBRARY_PATH.

The query and parameters generated is as follows.

SELECT "AUTHTOKEN_TOKEN"."KEY", "AUTHTOKEN_TOKEN"."USER_ID", "AUTHTOKEN_TOKEN"."CREATED",  
"AUTH_USER"."ID", "AUTH_USER"."PASSWORD", "AUTH_USER"."LAST_LOGIN", "AUTH_USER"."IS_SUPERUSER",  
"AUTH_USER"."USERNAME", "AUTH_USER"."FIRST_NAME", "AUTH_USER"."LAST_NAME", "AUTH_USER"."EMAIL",  
"AUTH_USER"."IS_STAFF", "AUTH_USER"."IS_ACTIVE", "AUTH_USER"."DATE_JOINED" FROM "AUTHTOKEN_TOKEN" INNER  
 JOIN "AUTH_USER" ON ("AUTHTOKEN_TOKEN"."USER_ID" = "AUTH_USER"."ID") WHERE "AUTHTOKEN_TOKEN"."KEY" =  
:arg0 FETCH FIRST 21 ROWS ONLY   

{':arg0': <django.db.backends.oracle.base.OracleParam object at 0x10b8497d0>} 

and the error I am seeing is

Traceback (most recent call last):
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/views/decorators/csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/viewsets.py", line 125, in view
    return self.dispatch(request, *args, **kwargs)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 480, in raise_uncaught_exception
    raise exc
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 497, in dispatch
    self.initial(request, *args, **kwargs)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 414, in initial
    self.perform_authentication(request)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/views.py", line 324, in perform_authentication
    request.user
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/request.py", line 227, in user
    self._authenticate()
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/request.py", line 380, in _authenticate
    user_auth_tuple = authenticator.authenticate(self)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/authentication.py", line 196, in authenticate
    return self.authenticate_credentials(token)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/rest_framework/authentication.py", line 201, in authenticate_credentials
    token = model.objects.select_related('user').get(key=key)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/models/query.py", line 425, in get
    num = len(clone)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/models/query.py", line 269, in __len__
    self._fetch_all()
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/models/query.py", line 1308, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/models/query.py", line 53, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
    cursor.execute(sql, params)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/kishorpawar/.virtualenvs/pl/lib/python3.7/site-packages/django/db/backends/oracle/base.py", line 535, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-00933: SQL command not properly ended

Can you please help me find what could be wrong here?

[cjbj]

Is this related to the original question asked on this issue? If not, perhaps you could open a new issue? On the technical side, check that you are connecting to the same DB versions in both. That FETCH FIRST 21 ROWS ONLY syntax was new with Oracle DB 12.1.

[kishorpawar]

I installed instant client libraries of version 11_2 and added them to LD_LIBRARY_PATH.

I assume 11_2 is compatible with Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Still the same issue.

[cjbj]

If your database is 11.2 then it won't understand the FETCH FIRST 21 ROWS ONLY syntax because the DB is too old - I already mentioned that the syntax was new with 12.1. You should follow up with the Django team. This repo here is only for cx_Oracle driver.

Also you should/could use Oracle XE 18c https://www.oracle.com/database/technologies/xe-downloads.html