ORA-01461 e VARCHAR2

Più volte mi è stato segnalato sulla nostra applicazione l’errore ORA-01461, il cui testo è “can bind a LONG value only for insert into a LONG column“. Prima di tutto è curioso che abbia dovuto risalire fino alla documentazione della versione 9.2 del database per rintracciare la documentazione dell’errore, quando tale errore l’ho riprodotto fino alla versione 11.2.0.2.

Altra cosa bizzarra e che forse spiega la rimozione dalla documentazione ufficiale di tale errore è che nei nostri database il tipo LONG cui si riferisce il testo del messaggio di errore non è mai esistito.

Un indizio che mi aiutò all’inizio a capire da cosa potesse derivare questo errore è il fatto che l’errore abbia fatto le prime comparse dopo che siamo passati sui nostri database al character set UTF-8 (mentre prima si usava ISO-8859 o equivalente). Potrei dedicare un intero post solo a descrivere le mie peripezie a cercare di fare test con la gestione di caratteri non ASCII con il character set UTF-8 ma per ora soprassiedo.

Oltre alle difficoltà a fare dei test con l’UTF-8, per la cui gestione da windows ho ancora delle difficoltà che ho aggirato usando putty e collegandomi su macchine linux, sono stato ingannato dal comportamento di funzioni come RPAD che fa cose come questa:

SVILUPPO42@perseo10 > select length(rpad('a',4500,'b')) from dual;

LENGTH(RPAD('A',4500,'B'))
--------------------------
4000

Comportamento del tutto lecito e anche logico, ritornando tale funzione un tipo dato VARCHAR2 che ha come limite appunto 4000 (byte), ma non essendo espressamente specificato nella documentazione non davo per scontato. Poi sulle funzioni SQL in Oracle occorre sempre fare attenzione, se sono usate in SQL valgono delle regole, in PL/SQL le cose sono leggermente diverse (e un varchar2 in PL/SQL può arrivare a 32767 (byte)

Riassumendo riporto le prove che ho fatto:

SQL> create table test_char (a varchar2(1300 char));

Table created.

Primo tentativo di aggirare il limite di RPAD

SQL> insert into test_char values (rpad('a',4000,'b')||'test');
insert into test_char values (rpad('a',4000,'b')||'test')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Al che ho deciso di provare con bind variables ma ho incontrato un’altra anomalia:

SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 28 14:44:27 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> var v_test varchar2(4000 char);
SP2-0676: Bind variable length cannot exceed 1000 characters

Il che è dovuto, come ho scoperto in seguito, semplicemente al fatto che avevo settato (erroneamente) la variabile d’ambiente

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Quindi ho deciso di passare a un test con PL/SQL:

SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',2000,'a');
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST_CHAR"."A" (actual: 2000,
maximum: 1300)
ORA-06512: at line 5

Dove si ottiene l’errore che mi aspetto. In realtà questo test funziona anche con una insert diretta con RPAD direttamente via SQL, ma il caso interessante è questo:

SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',4000,'a')||'test';
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 5

Che è l’errore che cercavo. Ho cercato di analizzare il trace generato attivando l’evento 10046 ma non ci ho trovato nulla di interessante.  L’errore si verifica anche da Java con driver JDBC, parrebbe che essendo una  stringa con lunghezz superiore ai 4000 caratteri la variabile venga convertita in tipo long e da ciò deriva l’errore, è una supposizione che faccio.