Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#20201 closed Bug (duplicate)

Oracle String Length Issue with CLOB and NCLOB

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

Description

As mentioned in #20200, we are moving from MySQL to Oracle, but are having issues with TextFields. When the length of a field is greater than 2000, I get the following error:

*** DatabaseError: ORA-01461: can bind a LONG value only for insert into a LONG column

I am writing a script using Django's ORM to copy the data from one database to the other. Once it gets to a row with more than 2000 characters, it throws that error. I have figured out a workaround, but it's ugly and not a good solution.

This is a part of my scripts (pseudo-code):

for row in Model.objects.using(source).all():
    row.save(using=target)

This is my workaround (again, very ugly):

for row in Model.objects.using(source).all():
    try:
        row.save(using=target)
    except:
        desc = row.description
        row.description = desc[:2000]
        row.save(using=target)

        # once you have a "good" save, you can now save the original data with no issues
        row.description = desc
        row.save(using=target)


My lab environment:

Windows 7
Python 2.7 (x86)
cx_Oracle 5.1.2
Oracle 11.2g (running in Exadata cluster)
Django 1.5.1

Change History (8)

comment:1 by Ross Peoples, 11 years ago

Cc: Ross Peoples added

comment:2 by Ross Peoples, 11 years ago

This appears to be a Django regression. Out of curiosity, I went back to Django 1.4.3 and it works fine without any workaround.

comment:3 by Ross Peoples, 11 years ago

Severity: NormalRelease blocker

comment:4 by Shai Berger, 11 years ago

Cc: shai@… added

There seems to be a problem here, but probably not the one you name.

First of all, how do you get values longer than 2000? What is the field definition?

Second, what is the column definition in Oracle? What was it in MySql?

Third -- when things work "with no issues" -- did you check that the whole value was saved, and not just the first 2000 characters?

Thanks,
Shai.

comment:5 by Aymeric Augustin, 11 years ago

Resolution: needsinfo
Status: newclosed

There's a long history of ORA-01461 errors: see #304, [ed5eca59], #9152, #11487.

However, the limit is usually 4000 chars, not 2000.

If your problem isn't a duplicate of any of these tickets, please provide the information requested by Shai and reopen.

Thank you.

comment:6 by Erin Kelly, 11 years ago

I can confirm that this bug is still around. The backend marks strings that are longer than 4000 characters as a CLOB when sending them to the database. The problem is that the limit is actually 4000 bytes, not 4000 chars. Consequently, as a practical matter the limit depends on the particular national character set used by the database, and for a variable-length encoding the particular characters used in the string would matter also. The only way I can see to generally fix it is to (expensively) test-encode the string just to determine its length before sending it. More practically, we should probably change the threshold from 4000 to 1000 characters.

comment:7 by Shai Berger, 11 years ago

Hi Ian,

I'd like to work on fixing Oracle bugs, but from the current description it was very unclear to me what the bug is and how to reproduce it. While your comment suggests that this is clear to you, it doesn't quite make it clear to me.

Would you mind trying to fix the description?

Thanks,
Shai.

comment:8 by Aymeric Augustin, 11 years ago

Resolution: needsinfoduplicate

Ian: if I understand correctly, #11487 is the canonical bug about this issue?

Note: See TracTickets for help on using tickets.
Back to Top