0 Replies Latest reply: Oct 4, 2011 11:25 AM by 811673 RSS

    Data of column datatype CLOB is moved to other columns of the same table

    811673
      Hi all,
      I have an issue with the tables having a CLOB datatype field.

      When executing a simple query on a table with a column of type CLOB it returns error [POL-2403] value too large for column.

      SQL> desc od_stock_nbcst_notes;
      Name Null? Type
      ------------------------------- ----- -------
      OD_STOCKID N NUMBER
      NBC_SERVICETYPE N VARCHAR(40)
      LANGUAGECODE N VARCHAR(8)
      AU_USERIDINS Y NUMBER
      INSERTDATE Y DATE
      AU_USERIDUPD Y NUMBER
      MODIFYDATE Y DATE
      VERSION Y SMALLINT(4)
      DBUSERINS Y VARCHAR(120)
      DBUSERUPD Y VARCHAR(120)
      TEXT Y CLOB(2000000000)
      NBC_PROVIDERCODE N VARCHAR(40)

      SQL> select * from od_stock_nbcst_notes;
      [POL-2403] value too large for column


      Checking deeply, some of the rows have got the data of the CLOB column moved in another column of the table.
      When doing select length(nbc_providercode) the length is bigger than the datatype of the field (varchar(40)).
      When doing substr(nbc_providercode,1,40) to see the content of the field, a portion of the Clob data is retrieved.



      SQL> select max(length(nbc_providercode)) from od_stock_nbcst_notes;
      MAX(LENGTH(NBC_PROVIDERCODE))
      -----------------------------
      162

      Choosing one random record, this is the stored information.

      SQL> select length(nbc_providerCode), text from od_stock_nbcst_notes where length(nbc_providerCode)=52;
      LENGTH(NBC_PROVIDERCODE) | TEXT
      -------------------------+-----------
      52 | poucos me

      SQL> select nbc_providerCode from od_stock_nbcst_notes where length(nbc_providerCode)=52;
      [POL-2403] value too large for column

      SQL> select substr(nbc_providercode,1,40) from od_stock_nbcst_notes where length(nbc_providercode)=52 ;
      SUBSTR(NBC_PROVIDERCODE
      ----------------------------------------
      Aproveite e deixe o seu carro no parque


      The content of the field is part of the content of the field text (datatype CLOB, containts an XML)!!!
      The right content of the field must be 'MTS' (retrieved from Central DB).


      The CLOB is being inserted into the Central DB, not into the Client ODB. Data is synchronized from CDB to ODB and the data is reaching the client in a wrong way.
      The issue can be recreated all the time in the same DB, but between different users the "corrupted" records are different.


      Any idea?