Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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?

Comment on No clear answer from Oracle
Select or Download Code
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.

    $dbh->do("ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'");
    -- 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 (Vicar) 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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://448334]
Approved by injunjoel
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (5)
As of 2014-12-21 22:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (108 votes), past polls