SQL*Loader: erreur chargement de date null [Résolu/Fermé]

Signaler
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
-
 iTechk -
Bonjour,
je n'arrive pas à importer une DATE qui peut être à null via sql loader de Oracle.
Voici mes différents essais dans le fichier de controle .ctl et la réponse de sqlldr :

mon premier champs DATE fonctionne :
"B_DATE" "to_date(:B_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
avec des données du genre Feb 14 2003

Le probleme se pose pour mon second champs qui est parfois NULL avec comme délimiteur la tabulation (FIELDS TERMINATED by X'09'). Dans mon exemple il est null à partir de la ligne 10441. Les premières passent bien.


essai 1 : "E_DATE" "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
=> Enregistrement 10441 : Rejeté - Erreur sur table "TFRC_TRMTH", colonne "E_DATE".
[...]
ORA-01843: ce n'est pas un mois valide
\\MAXIMUM ERROR COUNT EXCEEDED - Les statistiques données ci-dessus résultent d'une exécution partielle.
Table "TFRC_TRMTH" :
Chargement réussi de 10461 Lignes.
51 Lignes chargement impossible dû à des erreurs de données.

essai 2 : "E_DATE" NULLIF :E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
=> SQL*Loader-350: Erreur de syntaxe en ligne 25.
Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

essai 3 : "E_DATE" DATE NULLIF :E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
=> SQL*Loader-350: Erreur de syntaxe en ligne 25.
Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

essai 4 : "E_DATE" DATE "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
NULLIF :E_DATE='NULL'
=> ORA-01821: format de date inconnu

essai 5 : "E_DATE" "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
NULLIF :E_DATE='NULL'
=> SQL*Loader-350: Erreur de syntaxe en ligne 26.
"," ou ")" attendu, Mot-clé nullif trouvé.

essai 6 : "E_DATE" DATE TERMINATED by X'09' NULLIF :E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
=> Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

essai 7 : "E_DATE" DATE TERMINATED by X'09' NULLIF E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
=> SQL*Loader-403: La colonne référencée E_DATE n'apparaît pas dans la table "TFRC_TRMTH".

essai 8 : "E_DATE" "decode(:E_DATE,null,null,to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English'''))"
=> Enregistrement 10441 : Rejeté colonne "E_DATE".ORA-01843: ce n'est pas un mois valide

essai 9 : "E_DATE" decode(:E_DATE,null,null,"to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')")
=> Enregistrement 10441 : Rejeté colonne "E_DATE".ORA-01843: ce n'est pas un mois valide



Est ce que quelqu'un aurait la solution ? Je bute sur ce problème depuis 24h, et je commence à être à cours d'idées... En tous cas de bonnes idées ;-)

19 réponses

Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonsoir,

Peux-tu essayer le code suivant (trouvé sur internet après avoir cherché la syntaxe exacte)
"E_DATE" NULLIF :E_DATE=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

Je ne l'ai pas testé n'ayant pas Oracle sur mon PC.
A plus
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 86817 internautes nous ont dit merci ce mois-ci

Messages postés
969
Date d'inscription
samedi 11 octobre 2008
Statut
Membre
Dernière intervention
29 juillet 2010
362
Salut,
tu peux peut-être essayer cela:
"E_DATE" DATE "DECODE(:E_DATE, null, null, to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English'''))"
http://www.developpez.net/...
http://www.techonthenet.com/oracle/functions/decode.php
@+
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 86817 internautes nous ont dit merci ce mois-ci

Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

Je pense que l'essai 2 est le plus proche mais tu devrais peut-être le codé comme ci-dessous
"E_DATE" NULLIF E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')" 

En fait j'ai enlevé : devant E_DATE='NULL'. Je ne l'ai pas testé.
A plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
Merci de votre réponse, mais pas mieux :

Test : "E_DATE" NULLIF E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
Réponse : SQL*Loader-403: La colonne référencée E_DATE n'apparaît pas dans la table "TFRC_TRMTH".

il répond toujours ça quand on ne mets pas le :NOMCHAMPS
Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

Peux-tu copier/coller le contenu de ton fichier controle.ctl ?
Merci
A plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
Mille excuses je n'avais pas vu que vous aviez répondu ! Voici mon trmth.ctl :

-- TABLE oracle TFRC_TRMTH depuis sybase TRMTH
LOAD DATA
TRUNCATE
INTO TABLE "TFRC_TRMTH"
FIELDS TERMINATED by X'09'
optionally enclosed by '"'
TRAILING NULLCOLS
( "TRNS_ID" INTEGER EXTERNAL
, "BRCH_ID" INTEGER EXTERNAL
, "YEAR_" INTEGER EXTERNAL
, "NUM_TRNS" CHAR
, "LIAB" INTEGER EXTERNAL
, "TT_ID" CHAR
, "CONF" CHAR
, "REST" CHAR
, "CL_RADL" INTEGER EXTERNAL
, "CL_NAMEL" CHAR
, "CP_RADL" INTEGER EXTERNAL
, "CP_NAMEL" CHAR
, "ROL" CHAR
, "VAR_SIGN" CHAR
, "VAR" CHAR
, "TENOR" CHAR
, "B_DATE" "to_date(:B_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
, "E_DATE" NULLIF E_DATE='NULL' "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"
, "CISO" CHAR
, "AMT_1" INTEGER EXTERNAL
, "AMT_2" INTEGER EXTERNAL
, "AMT_3" INTEGER EXTERNAL
, "AMT_4" INTEGER EXTERNAL
, "AMT_5" INTEGER EXTERNAL
, "AMT_6" INTEGER EXTERNAL
, "AMT_7" INTEGER EXTERNAL
, "AMT_8" INTEGER EXTERNAL
, "AMT_9" INTEGER EXTERNAL
, "AMT_10" INTEGER EXTERNAL
, "AMT_11" INTEGER EXTERNAL
, "AMT_12" INTEGER EXTERNAL
, "AMT_E_1" INTEGER EXTERNAL
, "AMT_E_2" INTEGER EXTERNAL
, "AMT_E_3" INTEGER EXTERNAL
, "AMT_E_4" INTEGER EXTERNAL
, "AMT_E_5" INTEGER EXTERNAL
, "AMT_E_6" INTEGER EXTERNAL
, "AMT_E_7" INTEGER EXTERNAL
, "AMT_E_8" INTEGER EXTERNAL
, "AMT_E_9" INTEGER EXTERNAL
, "AMT_E_10" INTEGER EXTERNAL
, "AMT_E_11" INTEGER EXTERNAL
, "AMT_E_12" INTEGER EXTERNAL
, "AMT_D_1" INTEGER EXTERNAL
, "AMT_D_2" INTEGER EXTERNAL
, "AMT_D_3" INTEGER EXTERNAL
, "AMT_D_4" INTEGER EXTERNAL
, "AMT_D_5" INTEGER EXTERNAL
, "AMT_D_6" INTEGER EXTERNAL
, "AMT_D_7" INTEGER EXTERNAL
, "AMT_D_8" INTEGER EXTERNAL
, "AMT_D_9" INTEGER EXTERNAL
, "AMT_D_10" INTEGER EXTERNAL
, "AMT_D_11" INTEGER EXTERNAL
, "AMT_D_12" INTEGER EXTERNAL
)


Appel depuis un fichier import.cmd contenant (entre autre car j'ai 50 tables à passer de Sybase à Oracle)
SET ORACLE_PATH=D:\oracle\10.2.0\bin
SET CURRENTTABLE=trmth

%ORACLE_PATH%\SQLLDR %USEREDU% CONTROL=%REPTFRC%\ctl\%CURRENTTABLE%.ctl DATA=%REPTFRC%\data\%CURRENTTABLE%.txt BAD=%REPTFRC%\err\%CURRENTTABLE%.err LOG=%REPTFRC%\log\%CURRENTTABLE%.log >>%REPTFRC%\log\journal_sqlldr.log
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
Merci d'avoir cherché, hélas ça ne marche pas non plus. Voici la réponse :

SQL*Loader: Release 10.2.0.4.0 - Production on Jeu. Juil. 2 10:47:36 2009

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

SQL*Loader-350: Erreur de syntaxe en ligne 25.
Nombre entier positif ou Nom de colonne attendu, ":" trouvé.
, "E_DATE" NULLIF :E_DATE=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_D
             ^
Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

As-tu essayé aussi d'enlever le : devant E_DATE=BLANKS? L'erreur semble indiquée que SQL LOADER ne s'attend pas a trouvé ce caractère.

A plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
sans le : la colonne n'est jamais reconnue... Non ce que je comprends pas c'est que le mot clé NULLIF ne semble jamais être reconnu, qu'il soit placé avant ou après, alors que toutes les docs en parlent !

"E_DATE" NULLIF E_DATE=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

SQL*Loader-403: La colonne référencée E_DATE n'apparaît pas dans la table "TFRC_TRMTH".

"E_DATE" NULLIF :E_DATE=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

SQL*Loader-350: Erreur de syntaxe en ligne 25. Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

"E_DATE" DATE NULLIF :E_DATE=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

SQL*Loader-350: Erreur de syntaxe en ligne 25. Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

"E_DATE" "NULLIF :E_DATE=BLANKS to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

Enregistrement 1 : Rejeté - Erreur sur table "TFRC_TRMTH", colonne "E_DATE".
ORA-00917: virgule absente
Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

Peux-tu encore essayer ceci
"E_DATE" NULLIF "E_DATE"=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

car dans tous les exemples que j'ai trouvé, le nom des variables étaient toujours sans "
Merci et à plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
ça me paraissait être une excellente idée mais ça ne fonctionne pas non plus. J'ai aussi essayé d'utiliser le WHEN et aussi de supprimer l'option TRAILING NULLCOLS mais sans succes...


"E_DATE" DATE "DECODE(:E_DATE, null, null, to_date(:B_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

ORA-01821: format de date inconnu

"E_DATE" "DECODE(:E_DATE, null, null, to_date(:B_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

Enregistrement 1 : Rejeté - Erreur sur table "TFRC_TRMTH".
ORA-00917: virgule absente



WHEN "E_DATE" != ''
Chargement réussi de 10461 Lignes.
51 Lignes chargement impossible dû à des erreurs de données.
0 Lignes chargement impossible car échec de toutes les clauses WHEN.

WHEN "E_DATE" != null
SQL*Loader-350: Erreur de syntaxe en ligne 5.
Chaîne entre guillemets ou Identifiant HEX attendu, "null" trouvé.

WHEN "E_DATE" != "null"
Chargement réussi de 10461 Lignes.
51 Lignes chargement impossible dû à des erreurs de données.
0 Lignes chargement impossible car échec de toutes les clauses WHEN.

WHEN "E_DATE" != ""
Chargement réussi de 10461 Lignes.
51 Lignes chargement impossible dû à des erreurs de données.
0 Lignes chargement impossible car échec de toutes les clauses WHEN.
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
non j'ai parlé trop vite ! ça marche presque, il manquait une parenthese au decode et il fallait enlever le "date" :

"E_DATE" "DECODE(:E_DATE, null, null, to_date(:B_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English'''))"


maintenant mon fichier plante plus loin...

Un grand MERCI ! t'es un chef Christounet... trop fort !
Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

Il faut rendre à César ce qui est à César, la solution vient de Chuka et non de moi, mais content pour toi que ce problème là est résolu.
A plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
bah non finalement ça m'a décalé la colonne, et donc si ça plante plus loin (ligne 21328 au lieu de la 10441) c'est parceque le champs suivant ne peut pas recevoir le champs d'encore après...

oh galère...
Messages postés
969
Date d'inscription
samedi 11 octobre 2008
Statut
Membre
Dernière intervention
29 juillet 2010
362
Salut,
regarde ce lien:
http://www.developpez.net/...
il pourra peut-etre t'aider!!
@+
Messages postés
1265
Date d'inscription
mercredi 26 septembre 2007
Statut
Membre
Dernière intervention
29 juillet 2010
1 242
Bonjour,

As-tu essayé la solution que je t'ai donné dans le post N° 9, c-à-d
"E_DATE" NULLIF "E_DATE"=BLANKS "to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English''')"

A plus
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
Apparemment en modifiant le fichier source avec des pipes au lieu des tabulations puis en changeant mon fichier controle comme ça :

FIELDS TERMINATED by '|'
"E_DATE" "decode(:E_DATE, null, null, to_date(:E_DATE,'MON DD YYYY','NLS_DATE_LANGUAGE = ''English'''))"


et bien là ça fonctionne !!! mais je n'ai pas le choix de mes sources, sybase me crée des fichiers avec tabulation. Je vais donc utiliser sqlplus en ligne de commande dans mon fichier de lancement sous dos pour remplacer toutes les tabulations par |, j'espère que je ne devrai pas le faire dans mes 50 fichiers.

Apparement y'a pas d'autre solution, SQL Loader semble bugger sur un champs null s'il trouve deux tab côte à côte.

Encore merci pour votre aide, le DECODE etait une très bonne idée...
PS: le lien précédent n'apportait rien de neuf.
Messages postés
19
Date d'inscription
samedi 10 mars 2007
Statut
Membre
Dernière intervention
24 février 2014
4
réponse post 9 & 16 :

j'ai trouvé pourquoi le NULLIF ne fonctionnait jamais : ce n'est apparemment fait pour que pour les champs NUMBER ! Ce qui explique ce que le message d'erreur disait :
SQL*Loader-350: Erreur de syntaxe en ligne 26. Nombre entier positif ou Nom de colonne attendu, ":" trouvé.

cf. http://www.psoug.org/reference/sqlloader.html
"Using the NULLIF and BLANKS keywords to handle zero length strings being loaded into numeric columns."
Je sais que le problème semble résolu j'ai lu en diagonale le sujet mais NVL("",NULL) n'est-elle pas une solu° aussi