Comment convertir efficacement du texte en nombre dans Oracle PL / SQL avec une valeur par défaut NLS_NUMERIC_CHARACTERS?

J'essaie de trouver une façon efficace, méthode générique permettant de convertir de la chaîne en nombre de PL/SQL, où le paramètre locale pour NLS_NUMERIC_CHARACTERS paramètres est imprévisible -- et préférable que je ne vais pas y toucher. Le format d'entrée est la programmation standard "123.456789", mais avec un nombre inconnu de chiffres sur chaque côté de la virgule décimale.

select to_number('123.456789') from dual;
  -- only works if nls_numeric_characters is '.,'

select to_number('123.456789', '99999.9999999999') from dual;
  -- only works if the number of digits in the format is large enough
  -- but I don't want to guess...

to_number accepte un 3ème paramètre, mais dans ce cas, vous devez spécifier un deuxième paramètre de trop, et il n'y a pas de format de spec "par défaut"...

select to_number('123.456789', null, 'nls_numeric_characters=''.,''') from dual;
  -- returns null

select to_number('123.456789', '99999D9999999999', 'nls_numeric_characters=''.,''') from dual;
  -- "works" with the same caveat as (2), so it's rather pointless...

Il y a un autre chemin à l'aide de PL/SQL:

CREATE OR REPLACE
FUNCTION STRING2NUMBER (p_string varchar2) RETURN NUMBER
IS
  v_decimal char;
BEGIN
  SELECT substr(VALUE, 1, 1)
  INTO v_decimal
  FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
  return to_number(replace(p_string, '.', v_decimal));
END;
/

select string2number('123.456789') from dual;

qui ne exactement ce que je veux, mais il ne semble pas efficace si vous faites beaucoup, beaucoup de fois dans une requête. Vous ne pouvez pas mettre en cache la valeur de v_decimal (fetch fois et de le stocker dans une variable de package) car il ne sait pas si vous modifiez votre session de la valeur pour NLS_NUMERIC_CHARACTERS, et puis elle allait se briser, encore une fois.

Suis-je surplombant quelque chose? Ou suis-je trop se préoccuper, et Oracle n'est-ce beaucoup plus efficace, alors je vais donner tout le crédit?

source d'informationauteur bart

  1. 11

    La suivante devrait fonctionner:

    SELECT to_number(:x, 
                 translate(:x, '012345678-+', '999999999SS'), 
                 'nls_numeric_characters=''.,''') 
  FROM dual;

    Il va construire le bon deuxième argument 999.999999 avec l'efficace translate de sorte que vous n'avez pas à savoir combien de chiffres il y a à l'avance. Il va travailler avec toutes les éditions de Oracle format de nombre (jusqu'à 62 chiffres significatifs apparemment dans 10.2.0.3).

    Fait intéressant, si vous avez une très grande chaîne de la simple to_number(:x) travaillera alors que cette méthode échoue.

    Edit: soutien pour les nombres négatifs grâce à sOliver.

  2. 4

    Si vous faites beaucoup de travail par session, une option pourrait être d'utiliser
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
    au début de votre tâche.

    Bien sûr, si beaucoup d'autres code est exécuté dans la même session, vous pouvez obtenir funky résultats 🙂
    Cependant, nous sommes en mesure d'utiliser cette méthode dans notre base de données procédures de chargement, puisque nous avons consacré des programmes avec leurs propres pools de connexion pour charger les données.

  3. 3

    Désolé, j'ai remarqué plus tard que votre question était pour l'inverse. Néanmoins, il est à noter que pour la direction opposée, il y a une solution simple:

    Un peu en retard, mais aujourd'hui j'ai remarqué que le format spécial des masques TM9 " et "TME", qui sont décrits comme "le texte minimum format du numéro de modèle renvoie (dans la sortie décimale) le plus petit nombre de caractères possible." sur https://docs.oracle.com/cloud/latest/db112/SQLRF/sql_elements004.htm#SQLRF00210.

    Il me semble que si TM9 a été inventée pour résoudre ce problème particulier:

    select to_char(1234.5678, 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,''') from dual;

    Le résultat est '1234.5678' avec pas de chef de file ou les espaces à droite, et un POINT décimal, malgré mon environnement contenant NLS_LANG=GERMAN_GERMANY.WE8MSWIN1252qui serait normalement entraîner une VIRGULE.