A "specified length too long for its datatype ..." error occurs in the Create Data Source Wizard Advanced Advisor


In SAS® Text Miner, and in SAS® High-Performance Text Mining, truncation of a text variable on an Oracle database might occur. The 11g version of Oracle has a text-variable size-limit of 4000 characters. The Oracle limit applies only when reading the data on the appliance.

To ensure that you can use up to 4000 characters for a text variable, specify DBMAX_TEXT=4000 in the Oracle LIBNAME statement.

If text needs to be transcoded before it is recognized in the language that is defined in the SAS Configuration file, then you might exceed the 4000-character limit. In this situation, a message similar to the following is displayed in the Create Data Source Wizard, Advanced Advisor:

ORA-12801: error signaled in parallel query server P014, instance abcde.mycompany.com:exadat3 (3)ORA-00910: specified length too long for its datatype.
 
The error indicates that the DBMAX_TEXT value needs to be lowered to a value that is below 4000. A lower value enables using extra bytes that are needed during transcoding. Some characters might need multiple bytes per character when transcoding from the client encoding to the database server encoding.

A workaround for this issue is to specify 2 as the number of bytes per character within SAS® High-Performance Data Mining. To use this option in the SAS Enterprise Miner user interface, add this statement to the project start code:

%let hpdm_bytesperchar=2;