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
