AnsweredAssumed Answered

Oracle ORA-01401 when comparing long NVARCHAR2 columns with certain NLS settings

Question asked by matej1 on Sep 21, 2016
Latest reply on Sep 21, 2016 by matej1
Hello everyone,

we're getting errors for queries to our Oracle Express database version 11.2.0.2.0.

An example:


### Error querying database. Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column ### The error may exist in org/activiti/db/mapping/entity/HistoricProcessInstance.xml ### The error may involve org.activiti.engine.impl.persistence.entity.HistoricProcessInstanceEntity.selectHistoricProcessInstancesWithVariablesByQueryCriteria-Inline ### The error occurred while setting parameters ### SQL: select * from ( select a.*, ROWNUM rnum from ( select distinct RES.*, DEF.KEY_ as PROC_DEF_KEY_, DEF.NAME_ as PROC_DEF_NAME_, DEF.VERSION_ as PROC_DEF_VERSION_, DEF.DEPLOYMENT_ID_ as DEPLOYMENT_ID_, VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_, VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_, VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_, VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_ from ACT_HI_PROCINST RES left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_ left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null inner join ACT_HI_VARINST A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ WHERE DEF.KEY_ = ? and A0.NAME_= ? and A0.VAR_TYPE_ = ? and A0.TEXT_ = ? order by VAR.LAST_UPDATED_TIME_ asc ) a where ROWNUM < ?) where rnum >= ? ### Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column

As far as I can tell, this happens when the following conditions are met:
  • NLS_SORT
    is set to
    'BINARY_CI'
    ,
  • NLS_COMP
    is set to
    'LINGUISTIC'
    ,
  • the length of any
    TEXT_
    column subject to comparison is 1000 or more,
  • and a comparison or sort is attempted against the column without conversion to
    CHAR
    .
If I change
NLS_SORT
or
NLS_COMP
to
'BINARY'
, it works.
If I truncate all
TEXT_
columns to 999 (but not 1000) characters, it works.
It also works in cases where the comparison is initiated as case insensitive (with
variableValueEqualsIgnoreCase()
), because that rewrites
TEXT_
to
lower(TEXT_)
, which converts the column type to
CHAR
. Just doing
to_char(TEXT_)
also works.

I don't know why this is so. It may be a bug in the database, but with Oracle's non existent documentation, it's hard to tell if this is by design or not. A possible solution is removing Activiti's
SET_NLS_LOGON
trigger, but is that safe? I'm not sure why the trigger is needed, as case insensitive comparisons already use
lower()
when comparing strings.

Thanks for reading,
Matej

Outcomes