Oracle String Length Issue with CLOB and NCLOB

[Ross Peoples]

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

[Ross Peoples]

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.

[Shai Berger]

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.

[Aymeric Augustin]

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.

[Erin Kelly]

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.

[Shai Berger]

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.

[Aymeric Augustin]

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