11 Replies Latest reply: Dec 10, 2012 4:29 AM by 974238 RSS

    wrong currency symbol

    974238
      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
          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
            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
              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
                Ok i will do that.
                Can i still keep NLS_CURRENCY as #?
                • 5. Re: wrong currency symbol
                  Sergiusz Wolicki-Oracle
                  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
                    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
                      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
                        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
                          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
                            Hi Sergiusz,

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

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