Problem

CLOB fields are incorrectly assigned a data_length by Oracle within the user_tab_columns view of 4000, which is then used by the DYNAMIC_ATTRIBUTE to create the extensible section. The result is an error when entering data with >4000 characters and a failure to save the record, despite the fact that a CLOB field should have no character limit.

 

This is also evident when building the extensible table, which will report the COlumn Size for the CLOB field as 4000.


To rectify this:

  1. Sign into SQL Plus (SQL Developer, Tora, Toad...) as the biotics_dlink user.
  2. Run the following update statement, replacing SPECIMEN_DESC with the appropriate column name:
    update DYNAMIC_ATTRIBUTE
    set COLUMN_DATA_TYPE = 'CLOB',
    COLUMN_SIZE = -1
    where dynamic_attribute_id = (SELECT DYNAMIC_ATTRIBUTE_ID FROM DYNAMIC_ATTRIBUTE WHERE COLUMN_NAME='SPECIMEN_DESC' AND COLUMN_DATA_TYPE='CLOB');
  3. COMMIT; the changes
  4. Run Grant/Refresh Database Privileges


Now, when viewing the CLOB field within the extensible section, note that the Column Size is -1 rather than 4000. The record will now save successfully when entering data with >4000 characters.