MemoryError appears when using cx-Oracle 7.2 on Oracle 12c (12.1.0.2.0) with Python 3.7 (3.7.4).

[Alexandru Rudi]

These are the queries being executed before the crash:

Query:  ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
Params:  []
Query:  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' TIME_ZONE = 'UTC'
Params:  []
Query:  SELECT 1 FROM DUAL WHERE DUMMY LIKE TRANSLATE(:arg0 USING NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)
Params:  {':arg0': 'X'}

This is the traceback output in the command line:

Traceback (most recent call last):
  File ".\manage.py", line 17, in <module>
    execute_from_command_line(sys.argv)
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\commands\inspectdb.py", line 34, in handle
    for line in self.handle_inspection(options):
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\core\management\commands\inspectdb.py", line 47, in handle_inspection
    with connection.cursor() as cursor:
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\base\base.py", line 256, in cursor
    return self._cursor()
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\base\base.py", line 233, in _cursor
    self.ensure_connection()
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection
    self.connect()
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\base\base.py", line 197, in connect
    self.init_connection_state()
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\oracle\base.py", line 259, in init_connection_state
    ['X'])
  File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-packages\django\db\backends\oracle\base.py", line 510, in execute
    return self.cursor.execute(query, self._param_generator(params))
MemoryError

The SELECT query returns 1 as a result if executed in Oracle SQL Developer so I assume the error is not in the execution of that command.
I don't know if it's a bug in Django, cx-Oracle or at some other level or if it's a configuration issue and have no clue how to proceed.

Database information:

• Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
• PL/SQL Release 12.1.0.2.0 - Production
• "CORE 12.1.0.2.0 Production"
• TNS for Linux: Version 12.1.0.2.0 - Production
• NLSRTL Version 12.1.0.2.0 - Production

Environment:

• Python 3.7.4 64-bit
• cx-Oracle 7.2.0
• Oracle Instant Client Base Windows x64 version 19.3 (zip format)

[Mariusz Felisiak]

I'm not able to reproduce this issue, but I don't think that's an issue in Django. At the beginning I would try to install instance client for the same version i.e. "Oracle Instant Client Base Windows x64 version 12.1.0.2.0" (I know that this should "theoretically" works with 19.3), you can also check Visual Studio compatibility.

To confirm that it is not related with Django you can try to reproduce this issue with using cx_Oracle.cursor:

import cx_Oracle
connection = cx_Oracle.connect("user", "password", "TNS")
cursor = connection.cursor()
cursor.execute(".....")

Please use one of support channels.

[Alexandru Rudi]

import cx_Oracle
connection = cx_Oracle.connect("user", "password", "TNS")
cursor = connection.cursor()
cursor.execute(".....")

I executed the query commands as run by inspectdb using directly cx-Oracle by following the steps specified above without changing anything else in the setup/environment and everything executed without any issues so it is not a Visual Studio, Oracle Instant Client or cx-Oracle issue.

[Mariusz Felisiak]

Probably it will work also with cursor wrapped by Django:

from django.db import connection:
with connection.cursor() as cursor:
    cursor.execute(....) 

I think that memory issue is related with more complicated query. init_connection_state() is used when Django establishes an connection, so it should crash not only in inspectdb.

[Jani Tiainen]

As instructed use same version of instant client as your database version. Sometimes oracle is really picky.

Also note that sql developer uses JDBC not OCI. You could try to use sqlplus which uses OCI and shoul give some clue if it is OCI thats acting funny here.

[Alexandru Rudi]

Tried using the same version of instant client as the database version (12.1.0.2.0). Error still appears.

[Alexandru Rudi]

Issue appears on Python 3.7 (tested with 3.7.0 and 3.7.4). Issue does not appear on Python 3.6 (tested with 3.6.8).

def __init__(self, connection):
        self.cursor = connection.cursor()
        #self.cursor.outputtypehandler = self._output_type_handler

Disabling that line in base.py in class FormatStylePlaceholderCursor removes the MemoryError. No other changes required to default Django 2.2 implementation.
Without the output type handler, the inspectdb command completed successfully and generated models for all the tables found in the database.
I do not know how to proceed any further and don't even know the negative effects of disabling that handler. Is is still required?

[Jani Tiainen]

Could you please check NLS settings from your db:

SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM NLS_INSTANCE_PARAMETERS;

Also note that following settings takes place in environment before connection is opened:

NLS_LANG=.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=TRUE

_output_type_handler is used to properly read numerical values from db. It shouldn't affect model creation but it has effect when reading values from db.

Also if you could please try older versions of cx_Oracle just to rule out that it's some bug in cx_Oracle itself.

[Mariusz Felisiak]

Thanks for investigation, but I still don't believe that it is an issue in Django, it's probably some cx_Oracle issue with outputtypehandler related with a specific database, environment, OS, etc. configuration. I agree with Jani you could try to check with previous versions of cx_Oracle i.e. 7.1, 7.0, 6.X. If it works with other versions of cx_Oracle then you should report this issue in ​https://github.com/oracle/python-cx_Oracle/issues.

[Alexandru Rudi]

Replying to Jani Tiainen:

Could you please check NLS settings from your db:

SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM NLS_INSTANCE_PARAMETERS;

From NLS_DATABASE_PARAMETERS:

PARAMETER	VALUE
NLS_RDBMS_VERSION	12.1.0.2.0
NLS_NCHAR_CONV_EXCP	FALSE
NLS_LENGTH_SEMANTICS	BYTE
NLS_COMP	BINARY
NLS_DUAL_CURRENCY	$
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_SORT	BINARY
NLS_DATE_LANGUAGE	AMERICAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_CALENDAR	GREGORIAN
NLS_NUMERIC_CHARACTERS	.,
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_CHARACTERSET	WE8ISO8859P1
NLS_ISO_CURRENCY	AMERICA
NLS_CURRENCY	$
NLS_TERRITORY	AMERICA
NLS_LANGUAGE	AMERICAN

From NLS_INSTANCE_PARAMETERS:

PARAMETER	VALUE
NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

The rest of the values are (null).

Also note that following settings takes place in environment before connection is opened:

NLS_LANG=.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=TRUE

Can these be configured from settings.py?

_output_type_handler is used to properly read numerical values from db. It shouldn't affect model creation but it has effect when reading values from db.

Also if you could please try older versions of cx_Oracle just to rule out that it's some bug in cx_Oracle itself.

Tested with 7.1 and 7.0, both worked without issue, so it seems to be a regression in cx_Oracle, even though I scanned their changelogs twice and no mention of a change that would impact this. Thank you for being understanding and guiding me in the right direction. I will raise a ticket on their end.

[Alexandru Rudi]

For anyone else encountering this issue the fix is included in cx_Oracle 7.2.1 as mentioned here: ​https://github.com/oracle/python-cx_Oracle/issues/330.