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 a coluna a ser ordena contém mais de 999 caracteres e o parâmetro de sessão é NLS_SORT=WEST_EUROPEAN. Segue abaixo a simulação para o problema.
SQL> 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
SQL> 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
SQL> 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
SQL> 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
SQL> alter session set nls_language=AMERICAN;
Session altered.
SQL> create table x (name varchar2 (4000));
Table created.
SQL> insert into x values
('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...');
1 row created.
SQL> select length(name) from x;
LENGTH(NAME)
------------
1000
SQL> alter session set nls_sort=BINARY;
Session altered.
SQL> select name from x order by name;
NAME
-----------------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...
1 row selected
SQL> alter session set nls_sort=WEST_EUROPEAN;
Session altered.
SQL> 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
-----------------------------------------------------------------------------------------
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...
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
Segue abaixo o trace gerado pela sessão.
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
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
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
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
*** 2007-03-04 22:21:08.906
=====================
PARSING IN CURSOR #2 len=40 dep=0 uid=36 oct=42 lid=36 tim=6147967642
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
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
*** 2007-03-04 22:21:55.187
XCTEND rlbk=0, rd_only=1
Bom, como no arquivo de trace gerado acima 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:
1) Alterar o código SQL e adicionar na cláusula ORDER BY a função SUBSTR.
select * from
order by substr([coluna 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.
Nenhum comentário:
Postar um comentário