topic Oracle ORA-01401 when comparing long NVARCHAR2 columns with certain NLS settings in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235475#M188605 <HTML><HEAD></HEAD><BODY><SPAN>Hello everyone,</SPAN><BR /><BR /><SPAN>we're getting errors for queries to our </SPAN><STRONG>Oracle Express database version 11.2.0.2.0</STRONG><SPAN>.</SPAN><BR /><BR /><SPAN>An example:</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR />### 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 &lt; ?) where rnum &gt;= ? ### Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><SPAN>As far as I can tell, this happens when the following conditions are met:</SPAN><BR /><UL><LI><PRE class="language-none line-numbers"><CODE>NLS_SORT<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE> is set to <PRE class="language-none line-numbers"><CODE>'BINARY_CI'<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE>,</LI><LI><PRE class="language-none line-numbers"><CODE>NLS_COMP<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE> is set to <PRE class="language-none line-numbers"><CODE>'LINGUISTIC'<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE>,</LI><LI>the length of any <PRE class="language-none line-numbers"><CODE>TEXT_<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE> column subject to comparison is 1000 or more,</LI><LI>and a comparison or sort is attempted against the column without conversion to <PRE class="language-none line-numbers"><CODE>CHAR<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE>.</LI></UL><SPAN>If I change </SPAN><PRE class="language-none line-numbers"><CODE>NLS_SORT<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> or </SPAN><PRE class="language-none line-numbers"><CODE>NLS_COMP<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> to </SPAN><PRE class="language-none line-numbers"><CODE>'BINARY'<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>, it works.</SPAN><BR /><SPAN>If I truncate all </SPAN><PRE class="language-none line-numbers"><CODE>TEXT_<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> columns to 999 (but not 1000) characters, it works.</SPAN><BR /><SPAN>It also works in cases where the comparison is initiated as case insensitive (with </SPAN><PRE class="language-none line-numbers"><CODE>variableValueEqualsIgnoreCase()<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>), because that rewrites </SPAN><PRE class="language-none line-numbers"><CODE>TEXT_<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> to </SPAN><PRE class="language-none line-numbers"><CODE>lower(TEXT_)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>, which converts the column type to </SPAN><PRE class="language-none line-numbers"><CODE>CHAR<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>. Just doing </SPAN><PRE class="language-none line-numbers"><CODE>to_char(TEXT_)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> also works.</SPAN><BR /><BR /><SPAN>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 </SPAN><PRE class="language-none line-numbers"><CODE>SET_NLS_LOGON<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> trigger, but is that safe? I'm not sure why the trigger is needed, as case insensitive comparisons already use </SPAN><PRE class="language-none line-numbers"><CODE>lower()<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN> when comparing strings.</SPAN><BR /><BR /><SPAN>Thanks for reading,</SPAN><BR /><SPAN>Matej</SPAN></BODY></HTML> Wed, 21 Sep 2016 13:38:12 GMT matej1 2016-09-21T13:38:12Z Oracle ORA-01401 when comparing long NVARCHAR2 columns with certain NLS settings https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235475#M188605 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 Wed, 21 Sep 2016 13:38:12 GMT https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235475#M188605 matej1 2016-09-21T13:38:12Z Re: Oracle ORA-01401 when comparing long NVARCHAR2 columns with certain NLS settings https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235476#M188606 <HTML><HEAD></HEAD><BODY><SPAN>Just a small correction: removing the trigger won't fully correct the issue, as the default </SPAN><CODE>NLS_SORT</CODE><SPAN> is set from </SPAN><CODE>NLS_LANGUAGE</CODE><SPAN>, and sorts don't work if it's anything but </SPAN><CODE>'BINARY'</CODE><SPAN>. Removing the trigger does fix comparisons clauses though.</SPAN><BR /></BODY></HTML> Wed, 21 Sep 2016 13:46:09 GMT https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235476#M188606 matej1 2016-09-21T13:46:09Z Re: Oracle ORA-01401 when comparing long NVARCHAR2 columns with certain NLS settings https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235477#M188607 <HTML><HEAD></HEAD><BODY><SPAN>Oh, another possible solution would be to convert all </SPAN><CODE>NVARCHAR2</CODE><SPAN> columns to </SPAN><CODE>VARCHAR2</CODE><SPAN>, and is the solution I'm currently leaning towards.</SPAN><BR /><BR /><SPAN>By browsing StackOverflow I got the impression that </SPAN><CODE>NVARCHAR2</CODE><SPAN> is more of a legacy feature for non Unicode databases.</SPAN><BR /></BODY></HTML> Wed, 21 Sep 2016 15:43:07 GMT https://connect.hyland.com/t5/alfresco-archive/oracle-ora-01401-when-comparing-long-nvarchar2-columns-with/m-p/235477#M188607 matej1 2016-09-21T15:43:07Z