Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

No clear answer from Oracle

by wazoox (Prior)
on Apr 15, 2005 at 20:29 UTC ( #448334=perlquestion: print w/replies, xml ) Need Help??
wazoox has asked for the wisdom of the Perl Monks concerning the following question:

HI dear fellow monks. I'm in trouble writing a pretty big application using Oracle. Here's what's going fishy : working in France, I need some text fields in the database to accept accents ( and friends). So I had to add $ENV{NLS_LANG}='french_france.WE8ISO8859P15'; Before I connect to Oracle. Well, unfortunately there is an annoying side effect : now Oracle returns the floating-point numbers with a comma... Yes, in France the official separator between the integer and floating part is a comma. So  print $toto which used to return 0.45678 now returns what perl calls a "non numeric value" :,45678. I managed that with an atrocious
$toto =~ s/,/\./
But isn't there a better (and less ugly) way?

Replies are listed 'Best First'.
Re: No clear answer from Oracle
by gam3 (Curate) on Apr 15, 2005 at 21:17 UTC
    From DBD::Oracle

    A slightly more subtle problem can occur with NUMBER types. The default NLS settings might format numbers with a fullstop (".") to separate thousands and a comma (",") as the decimal point. Perl will generate warnings and use incorrect values when numbers, returned and formatted as strings in this way by Oracle, are used in a numeric context. You could explicitly convert each numeric value using the TO_CHAR(...) function but that gets tedious very quickly. The best fix is to change the NLS settings. That can be done for an individual connection with the code below.

    -- gam3
    A picture is worth a thousand words, but takes 200K.
      Thank you. Actually I read quite a lot of this doc (may CLOB burn in hell for ever) but I missed this part :)
Re: No clear answer from Oracle
by erix (Parson) on Apr 15, 2005 at 21:21 UTC

    Can't you combine that NLS_LANG with setting NLS_NUMERIC_CHARACTERS (to '.,' or whatever you need), or set it with ALTER SESSION?

    (The NLS_NUMERIC_CHARACTERS value should contain decimal character and group separator.)

    update: changed to '.,'

      Thank you very much, I didn't know about these NLS_... whatever until today, so I still have a lot to learn :)
Re: No clear answer from Oracle
by starbolin (Hermit) on Apr 15, 2005 at 21:45 UTC

    If the regex works I'd stick with that. (I don't think it's ugly at all. From what I get of LW's writings that is what Perl was ment to do.) Number::Format::Calc allows you do work directly with arbitrary formats but It looks like a lot of syntactic overload to me. YMMV.

    s//----->\t/;$~="JAPH";s//\r<$~~/;{s|~$~-|-~$~|||s |-$~~|$~~-|||s,<$~~,<~$~,,s,~$~>,$~~>,, $|=1,select$,,$,,$,,1e-1;print;redo}
      Well the regexp "just works" but I feel uneasy with it (treating a number as a chain to convert it back to number... blech). I'll keep it if I can't tweak the $ENV... Thanks.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://448334]
Approved by injunjoel
[ambrus]: holli: wait, plastic or glass bottle?
holli switches OS
[holli]: Lidl only sells plastic
[Corion]: holli: Oooh, a twister top - so yes, that's a worse state of mind than it sounded ;)
[holli]: and since I outed myself anyway as a horrible person, to top it off, the bottle contains beer. alcohol free beer
[Discipulus]: nowaday such beers have also a good taste
[Corion]: holli: Heh - if you consider alcohol free beer as a not-so-sweet lemonade, you're not that bad off ;)
[holli]: actually, that's exactly how i look at it.

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (15)
As of 2017-09-25 12:59 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (280 votes). Check out past polls.