Sometimes when inserting data into an Oracle cache, or after switching a Denodo Virtual DataPort database cache to 'Use data types supporting UTF-8' you might receive the following error:
ORA-01461: can bind a LONG value only for insert into a LONG column
This is due to text data being inserted into the Oracle cache that is too long for the LONG field that has been generated for the view in the Oracle cache by the Virtual DataPort Server to support. Usually, these text fields are CLOB, NCLOB, BLOB, or VARCHAR with long character limit data in the underlying data source. By default, Denodo caches text values as VARCHAR(4000), unless the subtype of the field is longer, and some base views, from delimited files for example, may not have a subtype for text fields that reflect the size of all of the entries.
The solution to this problem is to define the subtype of the text field causing the error in the base view it is coming from. This will propagate the changes to all views built on top of that field. The subtype should be set to be a CLOB, NCLOB, BLOB, if that is the type in the underlying source, or simply VARCHAR with the length that it is in the underlying source from the Virtual DataPort Administration Tool.
Here we are changing the subtype of text field job_id to be a VARCHAR with length 8000:
Once you have changed the subtype of the field, you will need to reload the cache for the view that is raising the ORA-01461 error. This is done by opening the view and going to Options > Cache mode: Off saving the view and then changing the cache mode back to the previous configuration and saving the view again to confirm the changes. The table in the Oracle cache database will be automatically recreated with a field matching the subtype which you have just set in the view.