Publicité
+ Répondre à la discussion
Affichage des résultats 1 à 10 sur 10
  1. #1
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut Utilisation de TO_NUMBER

    Bonjour,

    Nous devons importer des données d'un fichier CSV vers une table Oracle puis exécuter une procédure qui va utiliser cette table.

    Dans le fichier CSV, des montants sont formatés de cette façon : "11*800,73".
    Les colonnes de montants sont importés dans des colonnes VARCHAR2 dans la table.

    Malheureusement, dans la procédure, le montant est affecté à une variable de type NUMBER. Je dois donc ajouter une conversion de VARCHAR2 en NUMBER(12, 2) dans la procédure pour qu'elle puisse fonctionner.

    Je n'arrive pas a exprimer correctement le format à donner à la fonction TO_NUMBER, en travaillant sur un seul montant pour test.
    Code :
    1
    2
    3
    SELECT TO_NUMBER(rar_mht_in, '9G999D99', 'NLS_NUMERIC_CHARACTERS= ,') AS rar_mht_in
    FROM envt.rar_cocw
    WHERE no_piece = '546'
    J'obtiens cette erreur :
    ORA-12702: la chaîne de paramètre NLS n'est pas valide dans la fonction SQL
    J'ai pris la syntaxe de NLS_NUMBER_CHARACTERS dans ce message.

    Auparavant, d'après la doc d'Oracle, j'avais essayé ceci :
    Code :
    1
    2
    3
    SELECT TO_NUMBER(rar_mht_in, '9G999D99', NLS_NUMERIC_CHARACTERS ' ,') AS rar_mht_in
    FROM envt.rar_cocw
    WHERE no_piece = '546'
    Et là j'obtenais ce message :
    ORA-00907: parenthèse de droite absente


    Les montants du fichier CSV vont de l'entier simple "400" à "160 006,08".

    EDIT
    Si je fais cette requête :
    Code :
    1
    2
    SELECT TO_NUMBER('6 000,00', '9G999D99')
    FROM DUAL
    J'obtiens : 6000

    Sachant que le montant de la no_piece est justement 6 000,00, si je fais cette requête :
    Code :
    1
    2
    3
    SELECT TO_NUMBER(rar_mht_in, '9G999D99') AS rar_mht_in
    FROM envt.rar_cocw
    WHERE no_piece = '546'
    J'obtiens :
    ORA-01722: Nombre non valide
    01722. 00000 - "invalid number"
    *Cause:
    *Action:
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  2. #2
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 832
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 832
    Points : 13 505
    Points
    13 505

    Par défaut

    Plusieurs remarques :
    1. C'est un varchar2 de combien en source ?
      En spécifiant le format il vaut mieux aller à la précision maximale.
    2. Ensuite et là ce n'est pas intuitif, la valeur du nls_numeric_caracters est une chaîne dans une chaîne, il faut donc doubler les quotes :
      Code :
      'NLS_NUMERIC_CHARACTERS='' .'''
    3. Enfin, il ne faut pas intervertir le caractère des milliers et de la décimale :
      Code :
      1
      2
      3
      4
      5
      6
      7
      8
      SELECT to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='' ,''') AS col FROM dual;
      -- ORA-01722: invalid number
       
      SELECT to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS col FROM dual;
       
      COL
      --------
      11800.73

  3. #3
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut

    Citation Envoyé par Waldar Voir le message
    Plusieurs remarques :
    C'est un varchar2 de combien en source ?
    En spécifiant le format il vaut mieux aller à la précision maximale.
    Bizarrement, celui qui a créé le machin a mis l'une des deux colonnes destinées à accueillir les montants en VARCHAR2(50 BYTE) et l'autre en VARCHAR2(20 BYTE).
    Quel doit être mon format optimal pour ces deux cas alors ?

    Ensuite et là ce n'est pas intuitif, la valeur du nls_numeric_caracters
    est une chaîne dans une chaîne, il faut donc doubler les quotes :
    Code :
    'NLS_NUMERIC_CHARACTERS='' .'''
    Alors ça je n'aurais jamais deviné après ce que j'ai lu dans la doc Oracle qui semble donc n'être pas du tout explicite sur ce point.

    Enfin, il ne faut pas intervertir le caractère des milliers et de la décimale :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='' ,''') AS col FROM dual;
    -- ORA-01722: invalid number
     
    SELECT to_number('11 800,73', '999G999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS col FROM dual;
     
    COL
    --------
    11800.73
    Là j'ai du mal comprendre la doc. Cela voudrait dire qu'il faut donner les NLS_NUMERIC_CHARACTERS non pas dans l'ordre où ils sont dans la chaîne de caractères (logiquement, le séparateur de milliers d'abord puis le séparateur décimal) mais dans l'ordre inverse ?

    Je n'ai pas Oracle à la maison pour tester (et pas envie de me farcir son installation) donc ça attendra lundi.

    Merci pour la réponse en tout cas.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  4. #4
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 832
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 832
    Points : 13 505
    Points
    13 505

    Par défaut

    Citation Envoyé par CinePhil Voir le message
    Quel doit être mon format optimal pour ces deux cas alors ?
    9G999G999G999G999D99 pour le 20 et beaucoup plus long pour le 50, à avoir si c'est utile quand même.


    Citation Envoyé par CinePhil Voir le message
    Là j'ai du mal comprendre la doc. Cela voudrait dire qu'il faut donner les NLS_NUMERIC_CHARACTERS non pas dans l'ordre où ils sont dans la chaîne de caractères (logiquement, le séparateur de milliers d'abord puis le séparateur décimal) mais dans l'ordre inverse ?
    En effet il faut fouiller un peu dans la doc.

    En recherchant dans TO_NUMBER :
    The 'nlsparam' argument in this function has the same purpose as it does in the TO_CHAR function for number conversions. Please refer to TO_CHAR (number) for more information.
    En suivant le lien :
    This argument can have this form:

    'NLS_NUMERIC_CHARACTERS = ''dg''
    d pour decimal, g pour group.

    Citation Envoyé par CinePhil Voir le message
    Je n'ai pas Oracle à la maison pour tester (et pas envie de me farcir son installation) donc ça attendra lundi..
    Je ne travaille plus sur Oracle depuis un an (snif), mais je me suis créé un compte sur apex.oracle.com pour tester les petites requête, c'est pratique.

  5. #5
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut

    En suivant le lien :
    This argument can have this form:

    'NLS_NUMERIC_CHARACTERS = ''dg''
    J'avais vu cet extrait mais ne l'avais pas interprété comme devant respecter l'ordre d puis g.

    Pas claire la doc !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  6. #6
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut

    Après avoir passé du temps à corriger quelques données, nous revenons à cette procédure qui nous pose problème...

    Au début de la procédure il y a maintenant ceci :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    -- Liste des RAR à créer dans jefyco
    CURSOR cur_rar IS
    SELECT rar_date_cre,
           no_piece,
           substr(nom_du_tiers,1,50) AS rar_nom_tiers,
           rar_cpt,
           rar_exo,
           rar_date_ema,
           rar_lib_objet,
           TO_NUMBER(rar_mht_in, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS rar_mht_in,
           TO_NUMBER(rar_mht_rest, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS rar_mht_rest,
           rar_relance,
           rar_no_jefyco
    FROM envt.rar_cocw 
    WHERE 
    import IS NULL;
    c1 cur_rar%ROWTYPE;
    En lançant la procédure, ou bien la requête SELECT toute seule, j'obtiens cette erreur :
    ORA-01722: Nombre non valide
    01722. 00000 - "invalid number"
    J'en conclus que mon TO_NUMBER n'est pas encore bon car quand je commente ces deux lignes, la requête s'exécute.

    Faut-il vraiment mettre un format de 20 caractères pour un varchar2(20 byte) ?
    C'est autre chose que j'ai mal interprété ?

    Pour mémoire, les montants dans les deux colonnes sont formatés de la sorte : 999 999,99
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 832
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 832
    Points : 13 505
    Points
    13 505

    Par défaut

    Il faut vérifier les données les plus longues pour s'assurer que le format est suffisant :
    Code :
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT rar_mht_in
      FROM envt.rar_cocw
     WHERE length(rar_mht_in) = (SELECT max(length(rar_mht_in)) FROM envt.rar_cocw);
     
    SELECT min(rar_mht_in), max(rar_mht_in)
      FROM envt.rar_cocw;
     
    SELECT rar_mht_rest
      FROM envt.rar_cocw
     WHERE length(rar_mht_rest) = (SELECT max(length(rar_mht_rest)) FROM envt.rar_cocw);
     
    SELECT min(rar_mht_rest), max(rar_mht_rest)
      FROM envt.rar_cocw;

  8. #8
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut

    A priori oui puisque le montant maxi de la première colonne est 600 000,00.
    Et dans la seconde les montants sont encore plus petits.

    Je viens de tester avec le premier montant de la première colonne :
    Code :
    1
    2
    SELECT TO_NUMBER('6 000,00', '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre 
    FROM DUAL
    => 6000


    Code :
    1
    2
    3
    SELECT TO_NUMBER(rar_mht_in, '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre
    FROM rar_cocw
    WHERE no_piece = '546'
    => ORA-01722: Nombre non valide


    Chose intéressante...
    Je copie le montant 6 000,00 dans SQL Developper et je le colle dans mon éditeur KWrite à la première requête :
    Code :
    1
    2
    SELECT TO_NUMBER('6*000,00', '999G999D99', 'NLS_NUMERIC_CHARACTERS='', ''') AS nombre 
    FROM DUAL
    => ORA-01722: Nombre non valide


    En regardant bien dans mon éditeur, au lieu d'un espace, j'ai une sorte de caractère de soulignement en gris clair !

    Y a t-il une fonction Oracle pour convertir une chaîne de caractères en code hexadécimal ou en code ASCII ?

    EDIT :
    Je pense que j'ai trouvé :
    Code :
    SELECT DUMP('6*000,00', 16) FROM DUAL
    => Typ=96 Len=8: 36,a0,30,30,30,2c,30,30
    Pour un espace, il devrait y avoir 20. Pas banal !

    EDIT 2 :
    J'ai collé cet espace bizarre dans la requête et j'ai eu les bon nombres.

    Ensuite, je l'ai collé dans les fonctions TO_NUMBER de ma procédure et elle a pu s'exécuter.


    Tordu ces importation de données d'un autre logiciel !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  9. #9
    Modérateur

    Homme Profil pro Fabien
    Ingénieur d'études en décisionnel
    Inscrit en
    septembre 2008
    Messages
    6 832
    Détails du profil
    Informations personnelles :
    Nom : Homme Fabien
    Âge : 36
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Ingénieur d'études en décisionnel
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : septembre 2008
    Messages : 6 832
    Points : 13 505
    Points
    13 505

    Par défaut

    Peut-être l'espace insécable (alt+0160) ?
    L'astérisque dans le premier sujet m'y avait fait pensé mais j'ai oublié par la suite - avec Chrome et sur ce forum, je vois des * au lieu des espaces insécables :
    1. Espace : " "
    2. Espace insécable : "*"

  10. #10
    Modérateur
    Avatar de CinePhil
    Homme Profil pro Philippe Leménager
    Ingénieur d'études en informatique
    Inscrit en
    août 2006
    Messages
    13 805
    Détails du profil
    Informations personnelles :
    Nom : Homme Philippe Leménager
    Âge : 51
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : août 2006
    Messages : 13 805
    Points : 23 054
    Points
    23 054

    Par défaut

    Probablement l'espace insécable en effet. Je ne sais pas à quelle étape de l'importation c'est apparu.
    En gros on part d'une exportation en format Excel à partir d'un ancien logiciel de comptabilité et comme à la DSI nous sommes 2 linuxiens, il a été ouvert et trituré en format ods avec Libre Office puis enregistré en csv pour importation dans Oracle.
    Et les espaces insécables figurent bien dans le fichier csv en tout cas !

    Voilà le genre de truc bien chronophage auquel peut être confronté un informaticien !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale de Formation Agronomique. Autoentrepreneur.
    Mon blog sur la conception des BDD, le langage SQL, le PHP avec Zend Framework...
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

+ Répondre à la discussion
Cette discussion est résolue.

Liens sociaux

Règles de messages

  • Vous ne pouvez pas créer de nouvelles discussions
  • Vous ne pouvez pas envoyer des réponses
  • Vous ne pouvez pas envoyer des pièces jointes
  • Vous ne pouvez pas modifier vos messages
  •