Bug no Oracle Database 10g Express Edition Release 10.2.0.1.0 ?

Olá,

Recentemente me deparei com um problema relacionado a um erro de banco de dados conhecido por muita gente [ORA-1401 inserted value too large for column] ao executar uma sentença SQL utilizando a cláusula ORDER BY. O problema é que este erro acontece somente no Oracle Database 10g Express Edition Release 10.2.0.1.0 (Western European) e nas condições onde o campo a ser ordenado contém mais de 999 caracteres e o parâmetro de sessão NLS_SORT=WEST_EUROPEAN.


LEGATTI> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

LEGATTI> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   R$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

LEGATTI> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

LEGATTI> select * from nls_instance_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

LEGATTI@XE> alter session set nls_language=american;

Session altered.

LEGATTI@XE> create table x (name varchar2 (4000));

Table created.

LEGATTI@XE> insert into x values
('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...');

1 row created.

LEGATTI@XE> select length(name) from x;

LENGTH(NAME)
------------
1000

LEGATTI@XE> alter session set nls_sort=BINARY;

Session altered.

LEGATTI@XE> select name from x order by name;

NAME
--------------------------------------------------------------------------------------------------- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

1 row selected

LEGATTI@XE> alter session set nls_sort=WEST_EUROPEAN;

Session altered.

LEGATTI@XE> select name from x order by name;

select name from x order by name
*
ERROR at line 1:
ORA-01401: inserted value too large for column

Como eu fiquei na dúvida por causa do sistema operacional (SUSE Linux) realizei um teste em um ambiente Windows:

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 ...

1 row selected.

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

Bem, como no arquivo de trace gerado não encontrei nada que pudesse apontar a causa do problema, não tive dúvidas de que é um bug relacionado ao Oracle Database 10g Express Edition Release 10.2.0.1.0. Para resolver este problema "quebra galho", encontrei duas possíveis soluções:

SOLUÇÕES
--------

1) Alterar o código SQL e adicionar na cláusula ORDER BY a função SUBSTR

select * from
order by substr(<campo com mais de 999 caracteres>,1,<valor <= 999>)

exemplo: select * from x order by substr(cod,1,100);


2) Criar uma TRIGGER de banco que altera o parâmetro de sessão toda vez que um usuário se conectar no banco de dados.

create or replace trigger trg_nls_sort
after logon ON schema
begin
execute immediate 'alter session set nls_sort=binary';
end;
/


Postei este problema no site askTom na esperança do Mr. Thomas Kyte conseguir desvendar o mistério. Fico no aguardo ....