Oracle error when filtering on one2many related TextField (nclob)

[c.po...@bangor.ac.uk]

Hi All

I hope someone can help with a really frustrating problem I'm having.

I have the following models 

class Output(models.Model):

    title = models.CharField(u'Title', max_length=1024, db_index=True)
    ingest_time = models.DateTimeField(u'Ingest timestamp', default=datetime.now())
    output_date = models.DateField(u'Output date', blank=True, null=True,  db_index=True)
    output_type = models.ForeignKey('OutputType')
    status = models.ForeignKey('Status')
    abstract = models.TextField(u'Abstract', blank=True, null=True)

class OutputMetadata(models.Model):

    output = models.ForeignKey('Output')
    field = models.ForeignKey('MetadataField')
    value = models.TextField(u'Field Value')

If i run this:

outputs = Output.objects.filter(outputmetadata__value='History')
list(outputs) #to make the lazy query run 

I get this error:

*** DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at line 1

(Note: Output.objects.all() does not return an error)

I took the generated sql and ran it in sqlplus and it works just fine. I also took the generated sql, stripped out the nclobs from the selected fields and ran it in cx_Oracle without django i.e. 

cursor.execute('SELECT "OUTPUTSAPP_OUTPUT"."ID" FROM "OUTPUTSAPP_OUTPUT" INNER JOIN "OUTPUTSAPP_OUTPUTMETADATA" ON ("OUTPUTSAPP_OUTPUT"."ID" = "OUTPUTSAPP_OUTPUTMETADATA"."OUTPUT_ID")  WHERE UPPER(DBMS_LOB.SUBSTR("OUTPUTSAPP_OUTPUTMETADATA"."VALUE")) = UPPER(\'History\')   ORDER BY "OUTPUTSAPP_OUTPUT"."OUTPUT_DATE" DESC') 

I get the same error.

I am running:

Django 1.4, cx_oracle 5.0.4 , Oracle 10.2 (which i am stuck with)

Has anyone seen anything like this or have any ideas where i can start looking for a solution? I've been googling but to no avail :-(

Any pointers much appreciated

Charlie

[Ian]

On Thursday, July 12, 2012 9:27:30 AM UTC-6, The Bear wrote:

I get this error:

*** DatabaseError: ORA-06502: PL/SQL: numeric or value error: character string buffer too small 
ORA-06512: at line 1

This is a database encoding issue; see:

https://code.djangoproject.com/ticket/11580

As I understand it, the DBMS_LOB.SUBSTR call is trying to take the first 4000 characters of some NCLOB and pack them into a 4000-byte buffer.  The problem arises when you're using an encoding where those 4000 characters take up more than 4000 bytes.  You might try the code patch in that ticket and see if it clears up the error.

Ian