This discussion is archived
11 Replies Latest reply: Dec 10, 2012 2:29 AM by 974238 RSS

wrong currency symbol

974238 Newbie
Currently Being Moderated
Guys,

We are loading the data from a csv file in oracle table.
We are using odi procedure which invlokes sqlloader through shell
script and loads the data.The only issue iam finding is in the file
we have a record like
GBP,DEBENHAMS,GREAT BRITAIN POUND STERLING,£,A,,,,
where you can see pound symbol clearly.
When we load the data into oracle table
it will becomes like ¿.
In the nls_session_parameters table we have like:-
NLS_CURRENCY £
NLS_LANGUAGE ENGLISH
NLS_ISO_CURRENCY UNITED KINGDOM
NLS_TERRITORY UNITED KINGDOM
Why it is loading like ¿. ?

Any ideas?
Cheers
Sri
  • 1. Re: wrong currency symbol
    ranit B Expert
    Currently Being Moderated
    It is because your Database Character-Set doesn't support this symbol.
    Most probably your Character-Set is UTF8 or US7ASCII.

    This pound symbol is supported by WE8MSWIN1252

    Please check this and reply...
    SELECT * FROM nls_database_parameters;
    and check for these parameter's values -
    nls_currency,
    nls_characterset,
    nls_language,
    nls_territory

    For me, the values are -
    NLS_LANGUAGE    AMERICAN
    NLS_TERRITORY    AMERICA
    NLS_CURRENCY    $
    NLS_CHARACTERSET    UTF8
    HTH
    Ranit B.

    Edited by: ranit B on Dec 5, 2012 5:24 PM
  • 2. Re: wrong currency symbol
    974238 Newbie
    Currently Being Moderated
    Hi,
    Thanks for your reply,
    i did check them it is here like:-

    NLS_LANGUAGE ENGLISH
    NLS_TERRITORY UNITED KINGDOM
    NLS_CURRENCY #
    NLS_CHARACTERSET AL32UTF8

    Any pointers here?

    Cheers
  • 3. Re: wrong currency symbol
    ranit B Expert
    Currently Being Moderated
    NLS_CHARACTERSET AL32UTF8

    Any pointers here?
    Could you please try altering this to WE8MSWIN1252?
    This might work.

    Or,
    try changing the column where this character is stored to NVARCHAR2
    Refer -- http://dba.stackexchange.com/questions/20016/euro-sign-symbol-not-showing-up-on-oracle-10gr2-and-aix-6-1

    As of now, let's keep the NLS_CURRENCY as '#'.
    Refer -- http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams136.htm

    Hope this was helpful,
    Ranit B.

    Edited by: ranit B on Dec 5, 2012 5:57 PM
  • 4. Re: wrong currency symbol
    974238 Newbie
    Currently Being Moderated
    Ok i will do that.
    Can i still keep NLS_CURRENCY as #?
  • 5. Re: wrong currency symbol
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Ranit,

    Your advise is wrong. Please, do not advise serious database changes or application redesign without first confirming the cause of the problem.


    Sri,

    AL32UTF8 is perfectly capable of supporting the pound symbol '£' as well as most other characters in everyday use in VARCHAR2 columns. Do not change anything in the database yet.

    The problem is, most probably, in the loading script. By default, the file to be loaded will be interpreted in US7ASCII, which is not capable of supporting the pound symbol, indeed. A bit confusing symptom is that the result of the loading process is the reversed question mark -- this is however possible if you look with an WE8ISO8859P1/WE8MSWIN1252 client at the loaded data. When looking with SQL Developer, you should see an "empty box" character.

    You need to set the environment variable NLS_LANG to ENGLISH_UNITED KINDGDOM.WE8MSWIN1252 before sqlldr is started or add the CHARACTERSET WE8MSWIN1252 clause to the sqlldr control file. The second option is valid only if the data is not in the control file itself. This advice assumes that the pound symbol in the file is encoded in WE8ISO8859P1 or its superset WE8MSWIN1252. It can also be encoded in AL32UTF8, in which case NLS_LANG and/or control file should use AL32UTF8 in place of WE8MSWIN1252. You can verify the encoding by opening the input file in a hex editor and checking the hex code of the pound symbol. If it is one byte 0xA3, use WE8MSWIN1252. If it is two bytes 0xC2 0xA3, use AL32UTF8.


    -- Sergiusz
  • 6. Re: wrong currency symbol
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    By the way, the NLS_CURRENCY value has no meaning for the encoding of the currency symbol. It is relevant for the TO_CHAR/TO_NUMBER conversion (explicit and implicit). Even then, NLS_DATABASE_PARAMETERS value is used for CHECK constraints and virtual columns. For queries and DML, NLS_SESSION_PARAMETERS matters.


    -- Sergiusz
  • 7. Re: wrong currency symbol
    ranit B Expert
    Currently Being Moderated
    Ok, I'm sorry. That won't happen again.

    But if he changes and something goes wrong, he can anytime revert it back write? Just had this concern.
  • 8. Re: wrong currency symbol
    974238 Newbie
    Currently Being Moderated
    Hi Sergiusz,

    Thanks for your reply.

    The control file dont have any data,if i want add this to control file, how to add this?
    Do you have any example?

    The second is we are calling sql loader shell script through ODI Procedure, so in case if want to set NLS_LANG to ENGLISH_UNITED KINDGDOM.WE8MSWIN1252 where we need to set this? Is it by using alter session set NLS_LANG=ENGLISH_UNITED KINDGDOM.WE8MSWIN1252?or can we set this though same shell script that call sql loader?Any example?

    It will be really helpful if you can give some info on the above things.

    Cheers
  • 9. Re: wrong currency symbol
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    The syntax of the SQL*Loader control file is documented here:
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_control_file.htm#i1006645
    and here:
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/app_ldr_syntax.htm#i631434

    An example:
    LOAD DATA
    CHARACTERSET WE8MSWIN1252
    INFILE 'sample.dat'
    BADFILE 'sample.bad'
    DISCARDFILE 'sample.dsc'
    APPEND
    INTO TABLE emp
    .
    .
    .
    The NLS_LANG variable can be set in the script
    #!/bin/sh
    .
    .
    .
    NLS_LANG="ENGLISH_UNITED KINGDOM.WE8MSWIN1252"; export NLS_LANG
    sqlldr ...
    .
    .
    .
    or
    #!/bin/csh
    .
    .
    .
    setenv NLS_LANG "ENGLISH_UNITED KINGDOM.WE8MSWIN1252"
    sqlldr ...
    .
    .
    .
    -- Sergiusz
  • 10. Re: wrong currency symbol
    974238 Newbie
    Currently Being Moderated
    Hi Sergiusz,

    Thanks for your reply.I wiil try them and let you know.

    cheers
  • 11. Re: wrong currency symbol
    974238 Newbie
    Currently Being Moderated
    Hi Sergiusz,
    Thanks mate,it is working perfectly.
    Cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points