Si vous avez une base Oracle qui utilise des formats de caractères sur plusieurs octets comme AL32UTF8, vous connaissez la différence entre BYTE et CHAR. Par défaut le paramètre nls_length_semantics est positionné à la valeur BYTE, ce qui signifie que lorsque vous créez une colonne de type VARCHAR2(X), elle peut contenir au maximum X octets et non pas X caractères. Voici une illustration de ce point avec le schéma SCOTT sur une base paramétrée en AL32UTF8:
create table T(col1 varchar2(2));
insert into T values ('éé');
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T"."COL1" (actual: 4, maximum: 2)
Pour éviter ce type de problèmes, vous pouvez positionner le paramètre nls_length_semantics à CHAR ou au choix préciser VARCHAR2(X CHAR) :
alter session set nls_length_semantics=CHAR;
drop table T purge;
create table T(col1 varchar2(2));
insert into T values ('éé');
1 row created.
select * from T
COL1
----
éé
Vous pouvez vérifier que la table contient bien un VARCHAR2(2 CHAR) simplement avec la commande desc:
SQL> desc T
Name Null? Type
---- ----- ----------------
COL1 VARCHAR2(2 CHAR)
Vous pouvez vérifier aussi utiliser le paramètre SIZE_BYTE_KEYWORD avec dbms_metadata:
set lines 32000
set serveroutput on buffer 20000
declare
handle NUMBER;
tr_handle NUMBER;
ret CLOB;
begin
-- Open a handle for tables
handle := DBMS_METADATA.OPEN('TABLE','11.1.0.7');
-- Get the SCOTT tables
DBMS_METADATA.SET_FILTER(handle, 'NAME','T');
DBMS_METADATA.SET_FILTER(handle, 'SCHEMA',user);
-- Display the command with the
-- DDL syntax
tr_handle := dbms_metadata.ADD_TRANSFORM(
handle, 'DDL');
dbms_metadata.SET_TRANSFORM_PARAM(tr_handle,
'SIZE_BYTE_KEYWORD', TRUE);
dbms_metadata.SET_TRANSFORM_PARAM(tr_handle,
'SEGMENT_ATTRIBUTES', FALSE);
dbms_metadata.SET_TRANSFORM_PARAM(tr_handle,
'STORAGE', FALSE);
dbms_output.enable;
ret:='X';
while (ret is not null) loop
-- Fetch the commands in a CLOB
ret := dbms_metadata.FETCH_CLOB(handle);
-- Print the SQL text
dbms_output.put_line(ret);
end loop;
-- Close the handle
dbms_metadata.CLOSE(handle);
end;
/
CREATE TABLE "SCOTT"."T"
( "COL1" VARCHAR2(2 CHAR)
)
Mais voilà, la taille maximum d’un VARCHAR2 est bien 4000 octets et pas 4000 caractères:
drop table T purge;
create table T(col1 varchar2(4000 char));
insert into T values (rpad('é',3000,'é')||'é');
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
2 réflexions sur “VARCHAR2(4000 char) ne permet pas de stocker 4000 caractères”
Pris en flag: j’ai triché!
ORA-12899 n’est en réalité pas du à la taille de la colonne de la table mais au fait que rpad(‘é’,3000,’é’) en AL32UTF8 contient une chaine de 4000 octets (rpad fait un trim) et que si je le concatène avec un autre caractère dans un ordre SQL, ca tente de faire de faire la concaténation dans un type varchar2, lequel échoue… puisqu’il ne peut pas contenir plus de 4000 octets; Peu importe le type de la colonne donc, l’ordre SQL génèrera la même erreur.
Voici un exemple qui fonctionne avec un type LONG ou CLOB…
drop table T purge;
create table T(col1 clob);
declare
gg varchar2(32000);
begin
gg:=rpad(‘é’,3000,’é’)||’é’;
insert into T values (gg);
end;
/
Malheureusement dans le cas d’un varchar2(4000), c’est également une erreur de conversion qui d’affiche:
drop table T purge;
create table T(col1 varchar2(4000));
declare
gg varchar2(32000);
begin
gg:=rpad(‘é’,3000,’é’)||’é’;
insert into T values (gg);
end;
/
ORA-01461: can bind a LONG value only for insert into a LONG column
Pourtant, ca fonctionne si la chaine a moins de 4000 octets:
declare
gg varchar2(32000);
begin
gg:=rpad(‘é’,10,’é’)||’é’;
insert into T values (gg);
end;
/
PL/SQL procedure successfully completed.
J’ai extrait un problème semblable que j’ai détecté avec DataPump:
KUP-11007: conversion error loading table « SCOTT ». »T »
ORA-12899: value too large for column COL1 (actual: 4000, maximum: 4000)
Bonjour,
Je rencontre la même erreur ORA-12899 avec une colonne de type LONG. Serait-ce dû à la même cause ?
Merci
Les commentaires sont fermés.