SQL Loader erreur avec .nextval

Résolu
Hellreaper -  
Bonjour,
Mon problème est que je n'arrive pas a importer mais ficher correctement,
Donc voici la table ke j'ai crée:

CREATE TABLE dmlres
(RESERVATION_ID NUMBER(6),
EMPLOYEE_ID NUMBER(6),
DATE_CREATION DATE,
TYPE_PAIEMENT VARCHAR2(30),
PRIX NUMBER(8,2),
ACHETEUR_ID NUMBER(6));

Voici mon ficher .ctl

LOAD DATA
INFILE 'C:\dmlres.csv'
TRUNCATE
INTO TABLE dmlres
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX ,
ACHETEUR_ID)

je crée ma sequence

CREATE SEQUENCE db_seq
START WITH 1
INCREMENT BY 1;

et je lance dans excuter cette commande:
sqlldr control=c:\dml_t_abo.ctl log=c:\log1.txt

Merci de me dire si vous trouver quelque chose de louche dans mon code merci,
Configuration: Windows Vista
Firefox 2.0.0.12

10 réponses

Résumé de la discussion

Un problème d'importation via SQL*Loader d'un fichier CSV dans une table Oracle, avec une colonne PRIX numérique, provoque une erreur ORA-01722 lors de la conversion. Plusieurs solutions ont été proposées, notamment ajuster le fichier de contrôle pour convertir PRIX avec to_number et NLS_NUMERIC_CHARACTERS, introduire des clauses WHEN pour les NULL, et utiliser TRAILING NULLCOLS. Des alternatives ont aussi été évoquées, comme modifier temporairement le paramétrage NLS_NUMERIC_CHARACTERS via une session Oracle, ou recourir à une solution Linux avec export NLS_NUMERIC_CHARACTERS. Des recommandations pratiques se démarquent pour adapter le fichier de contrôle et les conversions numériques, tout en demandant un exemple de ligne de données pour ajuster le chargement.

Bobot (l’IA à votre service)
  1. asevere Webmaster 
    ORA-01722:	invalid number
Cause:	The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
Action:	Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.


    Sinon, ça se complique un peu, mais comme je n'ai pas d'oracle pour tester, je ne sais pas si l'on peut faire ça plus simplement:
    LOAD DATA
INFILE 'C:\dmlres.csv'
TRUNCATE
INTO TABLE dmlres
WHEN PRIX<>'NULL'
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX CHAR "to_number(:PRIX,'9999999D99','NLS_NUMERIC_CHARACTERS= '',.''')",
ACHETEUR_ID)
INTO TABLE dmlres
WHEN PRIX='NULL'
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX,
ACHETEUR_ID)
    En gros, si le prix n'est pas la chaine 'NULL', on applique la conversion, sinon, on le traite normalement...

    Avec un peut de chance, ça passe... :)
    2
    1. Hellreaper
      Dsl pour ne repondre que maintemant j'ai un tp linux a rendre bientot ^^, merci pour ta reponse asevere je vais testé cela se weekend, encore merci.
      0
    2. asevere Webmaster
      @HellreaperPas de soucis

      Bon TP
      0
    3. Hellreaper
      @asevereBon, j'ai fini se fameut tp linux ^^, donc retournons a nos moutons, J'ai mis ce que tu m'avais dit de mettre dans mon ficher .ctl, mais malheureusement j'ai toujours des erreurs, Il ne ma pas crée un ficher bad cette fois ci parcontre je c pas pourquoi...
      pour tant j'ai bien mis :
      sqlldr control=c:\dml_t_abo.ctl log=c:\log1.txt bad=c:\bad.txt

      Enfin bref d'apres mon ficher log l'erreur viendrais du WHEN:

      Fichier de contrôle : c:\dml_t_res.ctl
      Fichier de données : C:\dmlres.csv
      Fichier BAD : bad.txt
      Fichier DISCARD : aucune spécification

      (Allouer tous les rebuts)

      Nombre à charger : ALL
      Nombre à sauter: 0
      Erreurs permises: 50
      Tableau de liens : 64 lignes, maximum de 256000 octets
      Continuation : aucune spécification
      Chemin utilisé: Classique

      Table DMLRES, chargé quand PRIX != 0X4e554c4c(caractère 'NULL')
      Option d'insertion en vigueur pour cette table : TRUNCATE

      Nom de colonne Position Long. Séparat. Encadrem. Type de données
      ------------------------------ ---------- ----- ---- ---- ---------------------
      RESERVATION_ID FIRST * ; CHARACTER
      chaîne SQL pour la colonne : "seq_t_reservations.nextval"
      EMPLOYEE_ID NEXT * ; CHARACTER
      DATE_CREATION NEXT * ; CHARACTER
      TYPE_PAIEMENT NEXT * ; CHARACTER
      PRIX NEXT * ; CHARACTER
      chaîne SQL pour la colonne : "to_number(:PRIX,'9999999D99','NLS_NUMERIC_CHARACTERS= '',.''')"
      ACHETEUR_ID NEXT * ; CHARACTER

      Table DMLRES, chargé quand PRIX = 0X4e554c4c(caractère 'NULL')
      Option d'insertion en vigueur pour cette table : TRUNCATE

      Nom de colonne Position Long. Séparat. Encadrem. Type de données
      ------------------------------ ---------- ----- ---- ---- ---------------------
      RESERVATION_ID NEXT * ; CHARACTER
      chaîne SQL pour la colonne : "seq_t_reservations.nextval"
      EMPLOYEE_ID NEXT * ; CHARACTER
      DATE_CREATION NEXT * ; CHARACTER
      TYPE_PAIEMENT NEXT * ; CHARACTER
      PRIX NEXT * ; CHARACTER
      ACHETEUR_ID NEXT * ; CHARACTER

      Enregistrement 13 : Rejeté - échec de toutes les clauses WHEN.
      Enregistrement 18 : Rejeté - échec de toutes les clauses WHEN.
      Enregistrement 23 : Rejeté - échec de toutes les clauses WHEN.
      Enregistrement 31 : Rejeté - échec de toutes les clauses WHEN.

      Table DMLRES :
      Chargement réussi de 37 Lignes.
      0 Lignes chargement impossible dû à des erreurs de données.
      4 Lignes chargement impossible car échec de toutes les clauses WHEN.
      0 Lignes chargement impossible car tous les champs étaient non renseignés.

      Table DMLRES :
      Chargement réussi de 0 Lignes.
      0 Lignes chargement impossible dû à des erreurs de données.
      41 Lignes chargement impossible car échec de toutes les clauses WHEN.
      0 Lignes chargement impossible car tous les champs étaient non renseignés.

      Espace affecté au tableau de liens : 198144 octets(64 lignes)
      Octets de tampon de lecture : 1048576

      Nombre total d'enregistrements logiques ignorés : 0
      Nombre total d'enregistrements logiques lus : 41
      Nombre total d'enregistrements logiques rejetés : 0
      Nombre total d'enregistrements logiques mis au rebut : 4

      Le début de l'exécution a été effectué sur Mar. Févr. 26 00:44:32 2008
      La fin de l'exécution a été effectuée sur Mar. Févr. 26 00:44:40 2008

      Temps écoulé (ELAPSED) : 00:00:09.22
      Temps processeur (CPU) : 00:00:00.07
      0
    4. asevere Webmaster
      @HellreaperTable DMLRES, chargé quand PRIX = 0X4e554c4c(caractère 'NULL')
      Table DMLRES, chargé quand PRIX != 0X4e554c4c(caractère 'NULL')

      A tout hasard, essayes avec des doubles quotes autour des NULLl dans les clause WHEN:
      LOAD DATA
INFILE 'C:\dmlres.csv'
TRUNCATE
INTO TABLE dmlres
WHEN PRIX<>"NULL"
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX CHAR "to_number(:PRIX,'9999999D99','NLS_NUMERIC_CHARACTERS= '',.''')",
ACHETEUR_ID)
INTO TABLE dmlres
WHEN PRIX="NULL"
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX,
ACHETEUR_ID)
      0
    5. asevere Webmaster
      @HellreaperNon, oublies ça changera rien....

      Peux-tu me montrer les lignes 13, 18, 23, 31 de ton fichier de données ?
      0
  2. asevere Webmaster
    Voilà, avec les logs c'est plus clair :)

    Alors, tu as plusieur solution (au moins 4) sans toucher à ton ora.ini

    Premièrement, ajouter un trigger after logon qui joue la requête suivante si le programme appelant est sqlloader/sqlloader.exe:
    execute immediate 'alter session set nls_numeric_characters= ",."'

    (Le programme appelant peut être récuperé entre autre par la requête suivante: 
    select program from v$session where audsid=sys_context('userenv','sessionid');)
    Deuxièmement, modifier ton fichier source pour passer d'office les bonnes valeurs à sqlloader

    Troisièmement, si tu es sous linux (pas testé sous windows qui est apparement ton environnement, avec l'équivalent de export via une console dos ça peut marcher...) setter la variable NLS_NUMERIC_CHARACTERS avant de lancer sqlloader, exemple: 
    $ export NLS_NUMERIC_CHARACTERS=",."
$ ta_commande_sqlloader
    Quatrième solution (la plus adaptée à mon gout), corriger ton fichier de control (.ctl) comme suit:
    LOAD DATA
INFILE 'C:\dmlres.csv'
TRUNCATE
INTO TABLE dmlres
FIELDS TERMINATED BY ';'
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
DATE_CREATION,
TYPE_PAIEMENT,
PRIX CHAR "to_number(:PRIX,'9999999D99','NLS_NUMERIC_CHARACTERS= '',.''')",
ACHETEUR_ID)
    Voilà! :)

    Tiens nous au courant, et si ça ne passe pas, il nous faudrait une ligne de donnée pour voir et le NLS_NUMERIC_CHARACTERS de la base. (+plus les nouvelles logs)

    @ plus
    1
    1. asevere Webmaster
      Bonjour,

      Quel est le message d'erreur retourné par la commande ?
      0
      1. Hellreaper
        Voici le ficher de log mais je crois que mon erreur n'est pas le .nextval mais dans ma colonne prix car j'ai des prix avec des virgules.
        or j'ai pour tant bien crée ma table non?

        SQL*Loader: Release 10.2.0.1.0 - Production on Mar. Févr. 19 17:42:58 2008

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

        Fichier de contrôle : c:\dml_t_abo.ctl
        Fichier de données : C:\dmlres.csv
        Fichier BAD : c:\dmlres.bad
        Fichier DISCARD : aucune spécification

        (Allouer tous les rebuts)

        Nombre à charger : ALL
        Nombre à sauter: 0
        Erreurs permises: 50
        Tableau de liens : 64 lignes, maximum de 256000 octets
        Continuation : aucune spécification
        Chemin utilisé: Classique

        Table DMLRES, chargé à partir de chaque enregistrement physique.
        Option d'insertion en vigueur pour cette table : TRUNCATE

        Nom de colonne Position Long. Séparat. Encadrem. Type de données
        ------------------------------ ---------- ----- ---- ---- ---------------------
        RESERVATION_ID FIRST * ; CHARACTER
        chaîne SQL pour la colonne : "seq_t_reservations.nextval"
        EMPLOYEE_ID NEXT * ; CHARACTER
        DATE_CREATION NEXT * ; CHARACTER
        TYPE_PAIEMENT NEXT * ; CHARACTER
        PRIX NEXT * ; CHARACTER
        ACHETEUR_ID NEXT * ; CHARACTER

        Enregistrement 1 : Rejeté - Erreur sur table DMLRES, colonne PRIX.
        ORA-01722: Nombre non valide
        .....
        0
        2. Hellreaper
          Voici mon ficher dmlres.csv si sa peut vous aider pour m'aider a trouver quesque je fait de faux :
          seq_t_reservations.nextval;9;05/11/2005;CB;289.2;1
          seq_t_reservations.nextval;7;03/11/2005;Cheque;165.5;6
          seq_t_reservations.nextval;4;29/10/2005;CB;588.4;23
          seq_t_reservations.nextval;7;25/10/2005;Espece;257.4;57
          seq_t_reservations.nextval;7;02/11/2005;CB;175;72
          seq_t_reservations.nextval;7;23/10/2005;Cheque;225;65
          seq_t_reservations.nextval;7;15/10/2005;Cheque;37.12;68
          seq_t_reservations.nextval;3;05/11/2005;CB;948.64;60
          seq_t_reservations.nextval;3;05/11/2005;Cheque;57;80
          seq_t_reservations.nextval;11;05/11/2005;Cheque;202.5;67
          seq_t_reservations.nextval;11;05/11/2005;CB;202.5;73
          seq_t_reservations.nextval;12;05/11/2005;CB;214.5;62
          0
          1. asevere Webmaster
            Alors?

            Le batch et toujours en train de tourner ? ou bien ? :)
            0
            1. Hellreaper
              Merci merci tu me sauve la vie!
              Mais j'ai une nouvelle erreur car dans mon ficher .csv certain prix on une valeur NULL et donc voici mon nouveau ficher de log:
              Ici l'erreur que j'ai si je comprend bien, c'est que je ne peut pas mettre une valeur NULL dans ma colone prix donc il faudrait que je fasse une contrainte sur ma table dmlres?

              SQL*Loader: Release 10.2.0.1.0 - Production on Mer. Févr. 20 17:51:18 2008

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

              Fichier de contrôle : c:\dml_t_abo.ctl
              Fichier de données : C:\dmlres.csv
              Fichier BAD : c:\dmlres.bad
              Fichier DISCARD : aucune spécification

              (Allouer tous les rebuts)

              Nombre à charger : ALL
              Nombre à sauter: 0
              Erreurs permises: 50
              Tableau de liens : 64 lignes, maximum de 256000 octets
              Continuation : aucune spécification
              Chemin utilisé: Classique

              Table DMLRES, chargé à partir de chaque enregistrement physique.
              Option d'insertion en vigueur pour cette table : TRUNCATE

              Nom de colonne Position Long. Séparat. Encadrem. Type de données
              ------------------------------ ---------- ----- ---- ---- ---------------------
              RESERVATION_ID FIRST * ; CHARACTER
              chaîne SQL pour la colonne : "seq_t_reservations.nextval"
              EMPLOYEE_ID NEXT * ; CHARACTER
              DATE_CREATION NEXT * ; CHARACTER
              TYPE_PAIEMENT NEXT * ; CHARACTER
              PRIX NEXT * ; CHARACTER
              chaîne SQL pour la colonne : "to_number(:PRIX,'9999999D99','NLS_NUMERIC_CHARACTERS= '',.''')"
              ACHETEUR_ID NEXT * ; CHARACTER

              Enregistrement 13 : Rejeté - Erreur sur table DMLRES, colonne PRIX.
              ORA-01722: Nombre non valide

              Enregistrement 18 : Rejeté - Erreur sur table DMLRES, colonne PRIX.
              ORA-01722: Nombre non valide

              Enregistrement 23 : Rejeté - Erreur sur table DMLRES, colonne PRIX.
              ORA-01722: Nombre non valide

              Enregistrement 31 : Rejeté - Erreur sur table DMLRES, colonne PRIX.
              ORA-01722: Nombre non valide

              Table DMLRES :
              Chargement réussi de 37 Lignes.
              4 Lignes chargement impossible dû à des erreurs de données.
              0 Lignes chargement impossible car échec de toutes les clauses WHEN.
              0 Lignes chargement impossible car tous les champs étaient non renseignés.

              Espace affecté au tableau de liens : 99072 octets(64 lignes)
              Octets de tampon de lecture : 1048576

              Nombre total d'enregistrements logiques ignorés : 0
              Nombre total d'enregistrements logiques lus : 41
              Nombre total d'enregistrements logiques rejetés : 4
              Nombre total d'enregistrements logiques mis au rebut : 0

              Le début de l'exécution a été effectué sur Mer. Févr. 20 17:51:18 2008
              La fin de l'exécution a été effectuée sur Mer. Févr. 20 17:51:24 2008

              Temps écoulé (ELAPSED) : 00:00:05.61
              Temps processeur (CPU) : 00:00:00.17
              0
              1. asevere Webmaster
                Ok, peux-tu nous copier-coller le contenu du fichier .bad généré ?
                0
                1. Hellreaper
                  voici mon ficher bad.txt:

                  seq_t_reservations.nextval;13;08/11/2005;NULL;NULL;46
                  seq_t_reservations.nextval;2;13/10/2005;NULL;NULL;43
                  seq_t_reservations.nextval;9;11/11/2005;NULL;NULL;46
                  seq_t_reservations.nextval;5;01/11/2005;NULL;NULL;19
                  0
                  1. asevere Webmaster
                    Bonjour, à prioris, si tu as créé ta table sans dire que la colonne était non-null (NOT NULL), tu peux insérer des valeurs null dedans, (si c'est ce que tu souhaites), tu peux toujours essayé de rajouter la clause TRAILING NULLCOLS dans ton fichier de contrôle:
                    ...
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(RESERVATION_ID "db_seq.nextval" ,
EMPLOYEE_ID ,
...
                    Mais de mémoire, ca converti un champs VIDE en NULL, donc ça ne devrait pas t'arranger, je pense que c'est le to_number qui quand il prend NULL pour le convertir déclanche cette erreur... Si je trouve quelque chose, je repasse.

                    A plus
                    0

                    Discussions similaires

                    Erreur 3005 sur France TV
                    Cambodgil

                    6 réponses
                    Problème streaming France 2
                    95 indomptable

                    1 réponse
                    Erreur s3 bbox wifi 7 bouygues
                    Fido62

                    14 réponses
                    Panne T32 sur telecommande Orange
                    rayseg22G

                    6 réponses
                    Erreur code 4001
                    contrariness

                    1 réponse