Taille des champs trop petite malgré MAX(LENGTH([sur tous les champs])) préalable

[Hecatonchar]

Bonjour à toutes et tous,

J'ai rencontré le souci suivant lors de l'export puis import d'une table via SQL Developer. Suite à l'exposé du problème j'expliquerais l'hypothèse que j'ai pu formuler quant au "pourquoi?". Si à tout hasard vous aviez un meilleur "pourquoi?" voire un "comment?" vous feriez de moi le plus heureux des newbies sur ce forum

J'ai donc une table que je souhaite exporter depuis Oracle pour l'importer sur une autre connexion (Oracle également). J'ai essayé avec plusieurs formats d'export mais mettons que je décharge ma table depuis la connexion A en .tsv, fichiers distincts.

Sous la connexion B je lance le script de création de table, une fois celle-ci créée je clique-droit dessus>Import data. Je vérifie les définitions des champs, leurs correspondances, tout semble OK, je lance la manip' et horreur: "Erreur: ORA-12899 valeur trop grande pour la colonne MON_SCHEMA.MA_TABLE.MA_COLONNE (réelle : 31, maximum : 30)".

Cette erreur se produit sur plusieurs champs (mais pas tous) de types variés (VARCHAR, DATE, NUMBER). En poursuivant la requête malgré les erreurs je constate que la différence de taille de chaîne (=réelle-maximum) n'excède pas les 5 ou 6 caractères.

Je me dis que le logiciel fait une estimation des tailles de champs sur les premiers enregistrements, je fais donc un MAX(LENGTH()) sur tous mes champs: les valeurs renvoyées correspondent bien aux définitions de champs fournies par Oracle lors du déchargement.

En y pensant en l'air je me dis qu'il est possible que certains caractères "prennent plus de place" dans le format d'export (ajout de caractères d'échappement pour les éventuels séparateurs contenus dans les champs, transformation des caractères spéciaux sous une forme moins ambigüe mais plus longue...).
Toujours est-il que de ne pas retomber sur mes pieds d'Oracle à Oracle me rend un peu chagrin (si Oracle modifie les données en export, pourquoi ne fait-il pas l'opération inverse lors de l'import?) et que surtout, pendant ce temps là, ben il n'y a rien qui est fait ^^.

Merci par avance pour votre attention et éventuels éléments de réponses.
Merci aussi à la communauté qui, sans le savoir, m'a souvent tiré de l'ornière auparavant.

Bien à vous,
H.

[Waldar]

Vous avez probablement vu juste.
Quel est le résultat de cette requête sur les deux environnements ?

Code :

1
2
3
SELECT *
  FROM sys.nls_database_parameters
 WHERE parameter IN ('NLS_LENGTH_SEMANTICS', 'NLS_CHARACTERSET')

[Hecatonchar]

Bonjour Waldar,

Merci beaucoup pour votre réaction

Dans l'environnement source (A) la requête renvoie:

PARAMETER	   VALUE
NLS_CHARACTERSET	AL32UTF8
NLS_LENGTH_SEMANTICS	BYTE

Dans l'environnement cible (B):

PARAMETER	   VALUE
NLS_CHARACTERSET	AL32UTF8
NLS_LENGTH_SEMANTICS	BYTE

(désolé si les sorties ne sont pas très lisibles, premier post de ma part et j'ignore donc encore beaucoup des habitudes et usages)

J'espère que cela pourra vous éclairer.
Je suppose cependant que la solution aurait été plus simple si la requête avait renvoyé des valeurs différentes dans chaque environnement? ~^

Cordialement,
H.

[Waldar]

Pas nécessairement, déjà vous êtes sur un character_set multibytes ça permet donc de poursuivre dans cette voie.
Je vous ai fait regarder le paramètre par défaut, mais celui-ci est contournable dans le script de création des tables.

Qu'il faut donc regarder :

Code :

1
2
3
4
5
SELECT owner, table_name, column_name, char_used, data_type
  FROM all_tab_columns
 WHERE owner      = <nom_user>
   AND table_name = <nom_table>
   AND data_type LIKE '%CHAR_';

[Hecatonchar]

Dans l'environnement source:

USERNAME	TABLE_SOURCE	AUTCOMINF	B	VARCHAR2
USERNAME	TABLE_SOURCE	AUTCOMEP	C	VARCHAR2
USERNAME	TABLE_SOURCE	AUTBASEP	C	VARCHAR2
USERNAME	TABLE_SOURCE	AUTBASINF	B	VARCHAR2
USERNAME	TABLE_SOURCE	GENRE		C	VARCHAR2
USERNAME	TABLE_SOURCE	CODTAXIN	B	VARCHAR2
USERNAME	TABLE_SOURCE	FAMILLE		B	VARCHAR2
USERNAME	TABLE_SOURCE	ESPECE		C	VARCHAR2
USERNAME	TABLE_SOURCE	NOMINFSP	C	VARCHAR2
USERNAME	TABLE_SOURCE	PARTS		B	VARCHAR2
USERNAME	TABLE_SOURCE	SECTEUR		B	VARCHAR2
USERNAME	TABLE_SOURCE	LOC		C	VARCHAR2
USERNAME	TABLE_SOURCE	COD_BOT_TDWG	B	VARCHAR2
USERNAME	TABLE_SOURCE	COD_UB_TDWG	B	VARCHAR2
USERNAME	TABLE_SOURCE	NOMLOC		C	VARCHAR2
USERNAME	TABLE_SOURCE	PRES_XY		C	VARCHAR2
USERNAME	TABLE_SOURCE	P_NUM_REC	C	VARCHAR2
USERNAME	TABLE_SOURCE	UNIT_ALT	B	VARCHAR2
USERNAME	TABLE_SOURCE	UNIT_POLINF	C	VARCHAR2
USERNAME	TABLE_SOURCE	UNIT_POLSUP	B	VARCHAR2
USERNAME	TABLE_SOURCE	CONTINENT	C	VARCHAR2
USERNAME	TABLE_SOURCE	DATCOMPL	C	VARCHAR2
USERNAME	TABLE_SOURCE	PAY		B	VARCHAR2
USERNAME	TABLE_SOURCE	NOT		C	VARCHAR2
USERNAME	TABLE_SOURCE	COD_ISO		B	VARCHAR2
USERNAME	TABLE_SOURCE	DESC		B	VARCHAR2

Et dans l'environnement cible:

USERNAME	TABLE_CIBLE	AUTCOMINF	B	VARCHAR2
USERNAME	TABLE_CIBLE	AUTCOMEP	C	VARCHAR2
USERNAME	TABLE_CIBLE	AUTBASEP	C	VARCHAR2
USERNAME	TABLE_CIBLE	AUTBASINF	B	VARCHAR2
USERNAME	TABLE_CIBLE	CODTAXIN	B	VARCHAR2
USERNAME	TABLE_CIBLE	FAMILLE		B	VARCHAR2
USERNAME	TABLE_CIBLE	ESPECE		C	VARCHAR2
USERNAME	TABLE_CIBLE	GENRE		C	VARCHAR2
USERNAME	TABLE_CIBLE	NOMINFSP	C	VARCHAR2
USERNAME	TABLE_CIBLE	PARTS		B	VARCHAR2
USERNAME	TABLE_CIBLE	SECTEUR		B	VARCHAR2
USERNAME	TABLE_CIBLE	LOC		C	VARCHAR2
USERNAME	TABLE_CIBLE	COD_BOT_TDWG	B	VARCHAR2
USERNAME	TABLE_CIBLE	COD_UB_TDWG	B	VARCHAR2
USERNAME	TABLE_CIBLE	NOMLOC		C	VARCHAR2
USERNAME	TABLE_CIBLE	PRES_XY		C	VARCHAR2
USERNAME	TABLE_CIBLE	P_NUM_REC	C	VARCHAR2
USERNAME	TABLE_CIBLE	UNIT_ALT	B	VARCHAR2
USERNAME	TABLE_CIBLE	UNIT_POLINF	C	VARCHAR2
USERNAME	TABLE_CIBLE	UNIT_POLSUP	B	VARCHAR2
USERNAME	TABLE_CIBLE	CONTINENT	C	VARCHAR2
USERNAME	TABLE_CIBLE	DAT_COMPL	C	VARCHAR2
USERNAME	TABLE_CIBLE	PAY		B	VARCHAR2
USERNAME	TABLE_CIBLE	NOT		C	VARCHAR2
USERNAME	TABLE_CIBLE	COD_ISO		B	VARCHAR2
USERNAME	TABLE_CIBLE	DESC		B	VARCHAR2

Pour information j'ai réussi à importer la table en passant tous les champs en VARCHAR(2000). Je débute en BDD mais ça ne ressemble pas à une "bonne pratique". De plus impossible de réduire les champs à posteriori (UPDATE d'une table à l'autre au sein du même environnement).

Si cela peut aider également, parmi les "valeurs" qui "dépassent", il y a des NULL ce qui me semble aberrant mais fera peut être du sens dans ce cas précis.

Encore merci pour votre aide,
H.


P.S. Je n'aurais plus accès aux environnements d'ici demain matin (et donc hélàs difficile de les interroger d'ici là).

[Waldar]

J'ai oublié de mettre les longueurs dans ma requête, enfin j'imagine que ce sont les mêmes.

Effectivement, tout mettre en varchar2(2000), ce n'est pas ce qu'il y a de mieux, on essaie d'ajuster.

[Hecatonchar]

Du coup j'ai lancé la requête suivante:

Code :

1
2
3
4
5
SELECT owner, table_name, column_name, char_used, data_type, data_length
  FROM all_tab_columns
 WHERE owner      =<username>
   AND table_name =<table_name>
   AND data_type LIKE '%CHAR_';

Ce qui donne au niveau source:

USERNAME	TABLE_SOURCE	AUTCOMINF	B	VARCHAR2	70
USERNAME	TABLE_SOURCE	AUTCOMEP	C	VARCHAR2	320
USERNAME	TABLE_SOURCE	AUTBASEP	C	VARCHAR2	120
USERNAME	TABLE_SOURCE	AUTBASINF	B	VARCHAR2	70
USERNAME	TABLE_SOURCE	CODTAXIN	B	VARCHAR2	7
USERNAME	TABLE_SOURCE	FAMILLE		B	VARCHAR2	30
USERNAME	TABLE_SOURCE	ESPECE		C	VARCHAR2	120
USERNAME	TABLE_SOURCE	GENRE		C	VARCHAR2	120
USERNAME	TABLE_SOURCE	NOMINFSP	C	VARCHAR2	120
USERNAME	TABLE_SOURCE	PARTS		B	VARCHAR2	30
USERNAME	TABLE_SOURCE	SECTEUR		B	VARCHAR2	3
USERNAME	TABLE_SOURCE	LOC		C	VARCHAR2	920
USERNAME	TABLE_SOURCE	COD_BOT_TDWG	B	VARCHAR2	3
USERNAME	TABLE_SOURCE	COD_UB_TDWG	B	VARCHAR2	2
USERNAME	TABLE_SOURCE	NOMLOC		C	VARCHAR2	200
USERNAME	TABLE_SOURCE	PRES_XY		C	VARCHAR2	8
USERNAME	TABLE_SOURCE	P_NUM_REC	C	VARCHAR2	20
USERNAME	TABLE_SOURCE	UNIT_ALT	B	VARCHAR2	2
USERNAME	TABLE_SOURCE	UNIT_POLINF	C	VARCHAR2	200
USERNAME	TABLE_SOURCE	UNIT_POLSUP	B	VARCHAR2	50
USERNAME	TABLE_SOURCE	CONTINENT	C	VARCHAR2	100
USERNAME	TABLE_SOURCE	DATCOMPL	C	VARCHAR2	100
USERNAME	TABLE_SOURCE	PAY		B	VARCHAR2	90
USERNAME	TABLE_SOURCE	NOT		C	VARCHAR2	4000
USERNAME	TABLE_SOURCE	COD_ISO		B	VARCHAR2	2
USERNAME	TABLE_SOURCE	DESC		B	VARCHAR2	250

Et au niveau cible:

USERNAME	TABLE_CIBLE	AUTCOMINF	B	VARCHAR2	70
USERNAME	TABLE_CIBLE	AUTCOMEP	C	VARCHAR2	320
USERNAME	TABLE_CIBLE	AUTBASEP	C	VARCHAR2	120
USERNAME	TABLE_CIBLE	AUTBASINF	B	VARCHAR2	70
USERNAME	TABLE_CIBLE	COD_TAXIN	B	VARCHAR2	7
USERNAME	TABLE_CIBLE	FAMILLE		B	VARCHAR2	30
USERNAME	TABLE_CIBLE	ESPECE		C	VARCHAR2	120
USERNAME	TABLE_CIBLE	GENRE		C	VARCHAR2	120
USERNAME	TABLE_CIBLE	NOMINFSP	C	VARCHAR2	120
USERNAME	TABLE_CIBLE	PARTS		B	VARCHAR2	30
USERNAME	TABLE_CIBLE	SECTEUR		B	VARCHAR2	3
USERNAME	TABLE_CIBLE	LOC		C	VARCHAR2	920
USERNAME	TABLE_CIBLE	COD_BOT_TDWG	B	VARCHAR2	3
USERNAME	TABLE_CIBLE	COD_UB_TDWG	B	VARCHAR2	2
USERNAME	TABLE_CIBLE	NOMLOC		C	VARCHAR2	200
USERNAME	TABLE_CIBLE	PRES_XY		C	VARCHAR2	8
USERNAME	TABLE_CIBLE	P_NUM_REC	C	VARCHAR2	20
USERNAME	TABLE_CIBLE	UNIT_ALT	B	VARCHAR2	2
USERNAME	TABLE_CIBLE	UNIT_POLINF	C	VARCHAR2	200
USERNAME	TABLE_CIBLE	UNIT_POLSUP	B	VARCHAR2	50
USERNAME	TABLE_CIBLE	CONTINENT	C	VARCHAR2	100
USERNAME	TABLE_CIBLE	DATCOMPL	C	VARCHAR2	100
USERNAME	TABLE_CIBLE	PAY		B	VARCHAR2	90
USERNAME	TABLE_CIBLE	NOT		C	VARCHAR2	4000
USERNAME	TABLE_CIBLE	COD_ISO		B	VARCHAR2	2
USERNAME	TABLE_CIBLE	DESC		B	VARCHAR2	250

Effectivement ça se ressemble beaucoup (trop), au moins Oracle effectue cette partie du travail de façon attendue ^^.
Une idée?

Cordialement,
H.

[Waldar]

Bon ce n'est pas ça.

Il reste à regarder les formats d'export des données date et numérique (surtout les nombres décimaux).
Pouvez-vous poster une seule ligne qui pose problème des données qui transitent ?

[Hecatonchar]

Bien sur, quelques-unes même (l'erreur se manifestant sur différents champs), sous quelle forme pour que ce soit lisible (environ 50 champs)?

Pour information j'avais ,avant d'identifier ce souci, les messages d'erreur suivant en cours d'import:

Line XX contains invalid enclosed character or data delimiter.

A priori il s'agissait de retours à la ligne dans les champs texte que j'ai résolu en faisant un REPLACE sur CHR(10) (en tout cas cette erreur n'arrive plus). Je ne pense pas que ce soit cette modification qui pose souci mais sait-on jamais...

[Rei Ichido]

Le problème vient a priori du passage par fichier.

Donc une possibilité serait déjà de ne pas passer par ces fichiers
N'est-il pas possible de faire un DBLink entre les 2 bases ? Cela te simplifierait grandement la vie !

Sinon, définir les longueurs de chaînes sur le nombre de caractères est plus compréhensible pour les développeurs / utilisateurs et évite ce genre de choses (on contrôle moins l'espace utilisé, of course, mais de toute façon dans le cas général ce n'est pas un problème) :

Code :

1
2
 
ALTER TABLE TABLE_SOURCE MODIFY AUTCOMINF VARCHAR2(70 CHAR);

Edit : en parlant de fichier, et en voyant que les retours à la ligne pouvaient poser problème, n'y a-t-il pas du ftp dans les transferts de fichier, et un petit souci éventuel de transfert en binaire/ascii ?

[Hecatonchar]

Bonjour Rei Ichido,

Effectivement un DBlink serait plus simple et sera probablement mis en place ultérieurement, suite à mon travail exploratoire (pas de bol, je sais ^^, j'aurais sans doute quitté les lieux d'ici là mais ça fera un argument de plus en faveur d'un DBlink, je ne manquerais pas de le faire savoir).

Pas de FTP dans mon processus (ou bien à l'insu de mon plein gré), je décharge les tables dans "Mes documents" et les importe (enfin j'essaye) depuis ce même dossier.

On m'a effectivement fait remarquer ce midi que j'avais probablement intérêt à spécifier mes tailles de champs en CHAR et non en BYTE (dans ce cas du moins, mais je note bien votre remarque à ce sujet et l'ajoute à ma liste de "bonnes pratiques à suivre"). Ces avis convergeant sont plutôt encourageants.

Je teste ça en espérant vous fournir le fin mot de l'histoire dans mon prochain message.

Merci à vous,
H.

[Hecatonchar]

Après plus d'une heure de chargement (et d'angoisse)...ma table semble importée à peu près correctement .

Dans quasiment tout les cas il a effectivement suffit de préciser que les longueurs de champs de type VARCHAR2 que je fournissais étaient en CHAR et non en BYTE.

Petit holà cependant: avant de finaliser la préparation de l'import j'avais encore deux champs qui "dépassaient" tout deux théoriquement en CHAR(1).

Après MAX(LENGTH()) , MAX(LENGTHB()) et tâtonnements j'ai pu poursuivre en fixant un champ en VARCHAR2(10) et l'autre en VARCHAR(2) alors que de toute évidence (voir ci-après) ils ne contiennent bien qu'un caractère chacun.

Estimant qu'il était possible que pour des raisons d'encodage mes champs "sautent" j'ai fait une petite vérification simpliste après coup en faisant des
SELECT DISTINCT sur les 2 champs concernés et les 4 champs les entourant présumant qu'en cas de perte de délimiteur je trouverais des valeurs plus folkloriques ou plus diverses entre la table d'origine et la table de destination. A priori pas de problèmes, de plus les deux tables comptabilisent le même nombre de lignes.

A tout hasard si quelqu'un a une méthode de validation plus adaptée, je suis bien sur preneur .

Enfin il reste, je pense, quelque chose de pas très clair dans cet affaire aussi si cela peut être utile à une partie de la communauté (qui n'a pas de DBlink par exemple ^^) je me propose de prolonger la discussion avec les résultats de tests d'export/import sur des données restreintes (à savoir à partir des SELECT DISTINCT sur mes deux champs à problème) pour identifier le(s) valeur(s) à problème. Si cela est nécessaire merci de me dire quelles autres informations pourraient être utiles pour les futurs lecteurs.

A tout hasard voici la liste des valeurs prises dans ces champs (hors NULL) si cela peut vous éclairer. Vous noterez en fin du champ ALT_PRECIS quelques valeurs dont on peut soupçonner qu'elles sont la cause de ces problèmes.

Valeurs prises par ALT_PRECIS:

9
7
6
5
4
3
2
1
0
x
f
é
e
E
c
~
]
?
>
=
<
-
&
"

Valeurs prises par SOUR_LAT_LONG:

2
1
x
w
W
v
s
S
n
m
l
f
e
E
c
C
.

Encore un grand merci à vous Rei Ichido et Waldar pour votre attention et vos précieux conseils.

Bien à vous,
H.