Opened 3 years ago

Closed 3 years ago

#20200 closed Bug (wontfix)

Oracle Inconsistencies with NVARCHAR2 and NCLOB

Reported by: Ross Peoples Owned by: nobody
Component: Database layer (models, ORM) Version: 1.5
Severity: Normal Keywords: oracle
Cc: Ross Peoples, shai@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: no
Easy pickings: yes UI/UX: no

Description

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.

Attachments (1)

creation_fix.diff (2.0 KB) - added by Ross Peoples 3 years ago.
oracle creation.py diff

Download all attachments as: .zip

Change History (7)

Changed 3 years ago by Ross Peoples

Attachment: creation_fix.diff added

oracle creation.py diff

comment:1 Changed 3 years ago by Ross Peoples

Needs documentation: unset
Needs tests: unset
Patch needs improvement: unset

The second issue (with NCLOB was not resolved with CLOB) was not resolved by this patch. I have discovered some very strange behavior with that and have created a new ticket for that: #20201

The submitted patch is still valid though for the max_length issue.

comment:2 Changed 3 years ago by Ross Peoples

Cc: Ross Peoples added

comment:3 Changed 3 years ago by Shai Berger

Cc: shai@… added
Needs documentation: set
Needs tests: set

Hi,

References to the conversations about switching nvarchar2 to varchar2 may be nice here. I haven't seen any such conversations lately, and I don't think I'd like Django deciding that it's ok to have strings that don't fit into fields (when the database CHARSET is not unicode).

W.r.t the patch: Especially given the claim that you are not doing an enhancement, but fixing a bug, please add a test that fails without your fix, and passes with it (I am not a core committer, and as I said, I am against your fix, but without tests and a documentation note about the change, the patch shouldn't even be considered by core).

comment:4 Changed 3 years ago by Tim Graham

Resolution: wontfix
Status: newclosed

Closing as "won't fix" given shai's objections and lack of follow-up from OP.

comment:5 Changed 3 years ago by Ross Peoples

Resolution: wontfix
Status: closednew

Disregard, a seperate bug report will be filed.

Last edited 3 years ago by Ross Peoples (previous) (diff)

comment:6 Changed 3 years ago by Ross Peoples

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top