0 Replies Latest reply: Jan 24, 2014 7:25 AM by mikelm RSS

    ORA-01401 select error when using NLS_COMP=LINGUISTIC ?

    mikelm

      Hello All.

       

      I observed a strange thing regarding case insensitive search in XE 11.2.0-1.0 and would like to know, if I am doing someting wrong, or if XE behaves wrong.

      I have tables with columns of type NVARCHAR2. If I do a simple select with a simple "like" whereclause, I get the ORA-01401 error, without having a single idea, where this error comes from. Here is my sceanrio:

       

      1) Create a simple table

           CREATE TABLE TTEST

           (

                ID NUMBER,

                SYM NVARCHAR2(30)

           );

       

      2) change your NLS_SORT and NLS_COMP settings to provide case insensitve search capabilities

           ALTER SESSION SET NLS_SORT=BINARY_CI;

           ALTER SESSION SET NLS_COMP=LINGUISTIC;

       

      3) do a simple select on this table:

           SELECT * FROM TTEST WHERE SYM LIKE N'm%';

       

      You will get the "ORA-01401 : inserted value to large for column" error.

       

      If you change your NLS_COMP setting to BINARY, the select is working fine again.

       

      Any idea what's going on here?

       

      Thanks for help and kind regards

      .............mikelm