Hi Tom,
Now, I am doing this test at home ... really strange because the trace file doesn't show me any error.
Are you be able to make this test and reproduce this error too ? Tank you.
SQL> alter session set nls_language=american;
Session altered.
SQL> desc x
Name Null? Type
---------------- -------- ----------------------------
COD VARCHAR2(4000)
SQL> select length(cod) from x;
LENGTH(COD)
-----------
3455
SQL> alter session set tracefile_identifier ="nls_sort_test";
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter session set nls_sort=
binary;
Session altered.
SQL> select * from x order by cod;
COD
--------------------------------------------------------------------------------
x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x
x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x
x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x
x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x ...
SQL> alter session set nls_sort=
west_european;
Session altered.
SQL> select * from x order by cod;
select * from x order by cod
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
C:\>
The xe_ora_3828_nls_sort_test.trc trace file*** TRACE DUMP CONTINUED FROM FILE ***
Dump file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3828_nls_sort_test.trc
Sun Mar 04 22:20:42 2007
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:457M/767M, Ph+PgF:1343M/1829M, VA:1704M/2047M
Instance name: xe
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 3828, image: ORACLE.EXE (SHAD)
*** 2007-03-04 22:20:42.968
*** ACTION NAME:() 2007-03-04 22:20:42.968
*** MODULE NAME:(SQL*Plus) 2007-03-04 22:20:42.968
*** SERVICE NAME:(SYS$USERS) 2007-03-04 22:20:42.968
*** SESSION ID:(38.24) 2007-03-04 22:20:42.968
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=36 oct=42 lid=36 tim=6122021411 hv=1569151342 ad='6e220ba0'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=15625,e=668,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6122021403
=====================
PARSING IN CURSOR #2 len=33 dep=0 uid=36 oct=42 lid=36 tim=6131586584 hv=588511850 ad='6e1193bc'
alter session set nls_sort=binary
END OF STMT
PARSE #2:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6131586576
EXEC #2:c=0,e=81,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6131588509
*** 2007-03-04 22:20:58.437
=====================
PARSING IN CURSOR #1 len=28 dep=0 uid=36 oct=3 lid=36 tim=6137491485 hv=2664726661 ad='6e22bf3c'
select * from x order by cod
END OF STMT
PARSE #1:c=0,e=197,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6137491477
EXEC #1:c=0,e=127,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6137494664
FETCH #1:c=0,e=524,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,tim=6137495729
FETCH #1:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6137497073
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=7 pr=0 pw=0 time=247 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=13836 op='TABLE ACCESS FULL X (cr=7 pr=0 pw=0 time=150 us)'
*** 2007-03-04 22:21:08.906
=====================
PARSING IN CURSOR #2 len=40 dep=0 uid=36 oct=42 lid=36 tim=6147967642 hv=3066815499 ad='6e2081a0'
alter session set nls_sort=west_european
END OF STMT
PARSE #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6147967634
EXEC #2:c=0,e=89,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6147970095
*** 2007-03-04 22:21:21.796
=====================
PARSING IN CURSOR #1 len=28 dep=0 uid=36 oct=3 lid=36 tim=6160853059 hv=2664726661 ad='6e22bf3c'
select * from x order by cod
END OF STMT
PARSE #1:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6160853050
EXEC #1:c=0,e=112,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6160856242
FETCH #1:c=0,e=2209,p=0,cr=5,cu=0,mis=0,r=0,dep=0,og=1,tim=6160859046
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=21 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=13836 op='TABLE ACCESS FULL X (cr=5 pr=0 pw=0 time=131 us)'
*** 2007-03-04 22:21:55.187
XCTEND rlbk=0, rd_only=1
For a while, I needed to create this trigger below to "resolve" temporarialy the problem ...
create or replace trigger trg_nls_sort
after logon ON schema
begin
execute immediate 'alter session set nls_sort=binary';
end;
/