ORA-01461 e VARCHAR2

venerdì 28 ottobre 2011 alle 28:03 | Pubblicato su Diario, PL/SQL, SQL | 2 commenti
Tag: , , , ,

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.

2 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ciao Cristian,
    ho incontrato questo problema molte volte.
    Il problema e’ che alcuni caratteri occupano più di un byte quando sono codificati in AL32UTF8 (es. il simbolo dell’EURO), mentre con Character Set equivalenti a ISO-8859 (es. WE8ISO8859P15) tutti i caratteri occupano sempre 1 byte.
    Quando inserisci una stringa di 4000 caratteri in un campo VARCHAR2, se c’è anche un solo carattere multi-byte la stringa occuperà 4001 byte e quindi verrà associata a un tipo dati diverso (es. LONG) durante la fase di binding (visto che che il limite massimo per i VARCHAR2 e’ 4000 byte, non 4000 caratteri). Da qui l’errore.
    Hai provato:

    ALTER SYSTEM SET EVENTS ‘1461 trace name context forever, level 4′;

    ?
    Se guardi bene il trace (livello 4 o superiore), noterai che la lunghezza del valore associato al bind va oltre 4000.

    Personalmente ho provato diverse soluzioni, ma dipende da che tipo di dati devi gestire.

    Se hai stringhe codificate in molti alfabeti diversi (es. italiano, cinese, indiano, ecc…) puoi:

    – limitare i VARCHAR2 a 1000 caratteri (in AL32UTF8 ogni carattere occupa massimo 4 byte). Es. VARCHAR2(1000 CHAR)
    – usare NVARCHAR2 (limite massimo 2000 caratteri). Non conviene se usi principalmente alfabeti europei perché tende a occupare più spazio (sempre due byte per ogni carattere)
    – usare CLOB (possibili problemi con performance e indici e probabilmente devi modificare l’applicazione)

    Se usi principalmente un singolo alfabeto e pochi caratteri multi-byte, puoi:

    – Cambiare l’applicazione in modo che limiti la stringa per numero di byte invece che per numero di caratteri. Per esempio stabilire un limite massimo di 4000 byte per stringhe che devono essere salvate in campi VARCHAR2. Questo potrebbe non essere semplice da implementare a livello tecnico – dipende dal linguaggio di programmazione che usi e da quanto sono bravi i tuoi programmatori ; )
    – Cambiare il limite dei campi da 4000 a, per esempio, 3500 caratteri. Molto spesso il limite 4000 e’ scelto arbitrariamente e non e’ realmente necessario. Limitando a 3500, ti dai un ragionevole margine di 500 byte per caratteri multi-byte.
    In questo caso devi cambiare l’applicazione a livello funzionale ma non a livello tecnico.
    – usare CLOB (stessi problemi di cui sopra)

    Per dati che hai già ci sono poche scelte: troncare i dati in modo che non occupino piu di 4000 byte o trasformare (traslitterare sarebbe più corretto) i caratteri multi-byte in modo che siano single-byte (es. usare “E” al posto del simbolo dell’EURO). Per il secondo metodo l’utility Unix “iconv” può essere utile.
    In entrambi i casi la scelta e’ spesso dolorosa : (

    Spero ti sia d’aiuto!

    Saluti

  2. Grazie per il contributo Kamal, buona l’idea di abilitare il trace dell’evento 1461, appena ho un attimo ci provo.


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: