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

Question

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 ;-)

Christounet · Answer

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

meliweb · Answer

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

Christounet · Answer

Bonjour,

Peux-tu copier/coller le contenu de ton fichier controle.ctl ?
Merci
A plus

meliweb · Answer

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

Christounet · Answer

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

meliweb · Answer

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
             ^

Christounet · Answer

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

meliweb · Answer

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

Christounet · Answer

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

chuka · Answer

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'''))" 
https://www.developpez.net/forums/d756498/bases-donnees/oracle/sql/sql-loader-concatenation-nullif/
https://www.techonthenet.com/oracle/functions/decode.php
@+

meliweb · Answer

ç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.

meliweb · Answer

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 !

Christounet · Answer

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

meliweb · Answer

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...

chuka · Answer

Salut,
regarde ce lien:
https://www.developpez.net/forums/d87517/bases-donnees/oracle/outils/sql-loader/sql-loader-probleme-date/
il pourra peut-etre t'aider!!
@+

Christounet · Answer

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

meliweb · Answer

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.

meliweb · Answer

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://psoug.org/reference/sqlloader.html
"Using the NULLIF and BLANKS keywords to handle zero length strings being loaded into numeric columns."

iTechk · Answer

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

SQL*Loader: erreur chargement de date null

19 réponses

Discussions similaires