Oracle Inconsistencies with NVARCHAR2 and NCLOB
|Reported by:||deejross||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.5|
|Cc:||deejross, shai@…||Triage Stage:||Unreviewed|
|Has patch:||yes||Needs documentation:||yes|
|Needs tests:||yes||Patch needs improvement:||no|
We are currently working on moving our MySQL database over to an Oracle Exadata 11.2g cluster. We've hit a few surprises so far, such as the 30 character limit on table names, but this one I can't fix without making a code change to Django's Oracle backend. We are having two issues: the NVARCHAR2 fields cannot have their max_length argument set to 4000, per the documentation because it's max is calculated using the NLS_NCHAR_CHARACTERSET. In a shared environment, this cannot be changed without affecting other databases in the cluster.
The other issue is with NCLOB not accepting strings longer than 2000 characters. In my experiements, even though this is supposed to work, it doesn't unless you truncate the string first, save that to the database, then try and save the longer version again. Very strange behavior and I don't know if it's an Oracle bug or a cx_Oracle bug. I am using cx_Oracle 5.1.2 with Python 2.7 x86 on Windows 7 for the lab environment.
I have managed to solve these issues by editing the django/db/backends/oracle/creation.py file and swapping all instances of NVARCHAR2 with VARCHAR2 and NCLOB with CLOB. I've also noticed a lot of conversation about switching these and it was my understanding that the decision had already been made to correct this by using above recommendation. I have attached the current patch I am using which resolves all issues.
Change History (7)
Changed 2 years ago by deejross
comment:1 Changed 2 years ago by deejross
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:5 Changed 17 months ago by deejross
- Resolution wontfix deleted
- Status changed from closed to new