9 Replies Latest reply: Sep 20, 2012 6:40 AM by Zoltan Kecskemethy RSS

    Character discrepancy issue while spooling data

    962612
      Hello All,

      I'm facing a character discrepancy issue while extracting data from db tables.

      I've written a PL/SQL code to spool some data to .txt file from my db tables and invoking SQL*plus from Unix machine and using SPOOL command then but when I'm getting the spooled file, the result set is a changed one from the one at back end.

      For example:

      At back end: SADETTÝN MANAJANS THOMPSON REKLAM ÝÞLERÝ A.Þ.

      In Spooled txt file : SADETTŸN MANAJANS THOMPSON REKLAM Ÿ LERŸ A. .

      If you look at the Ÿ character, it is a changed one and 'Þ' is absent from the spooled text. I want to preserve all the characters the way they are at back end.

      My db's character set:

      SELECT * FROM v$nls_parameters WHERE parameter LIKE 'NLS%CHARACTERSET'
      PARAMETER VALUE
      NLS_CHARACTERSET WE8ISO8859P1
      NLS_NCHAR_CHARACTERSET WE8ISO8859P1

      And Unix NLS_LANG parameter :

      $ echo $NLS_LANG
      AMERICAN_AMERICA.WE8ISO8859P1

      I tried changing NLS_LANG parameter to WE8ISO8859P9(Trukish characterset) but no help!

      Could anyone let me know the solution to this problem?

      Thanks in advance.

      Prashant

      Edited by: pps7sep on Sep 17, 2012 11:30 AM
        • 1. Re: Character discrepancy issue while spooling data
          Zoltan Kecskemethy
          Hi,

          Welcome to the forum!

          I suggest to setup similar character set as your locale has in your console.
          use
          locale
          to display locale setting of your terminal
          locale -a
          to list available locales if you wish to change. But I simple suggest to change your NLS_LANG to match your locale.
          e.g.
          use AMERICAN_AMERICA.AL32UTF8 if your locale is en_US.UTF8
          ...
          BTW see the following MOS doc for full details:
          [url https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=264157.1]Oracle Metalink: The correct NLS_LANG setting in Unix Environments Doc ID: 264157.1

          HTH, Zoltan
          • 2. Re: Character discrepancy issue while spooling data
            962612
            Hi Zoltan,

            Thanks for your suggestion.

            Unfortunately, I don't have access to the link posted by you.

            Here are my locale settings:

            $ locale
            LANG=
            LC_CTYPE="C"
            LC_NUMERIC="C"
            LC_TIME="C"
            LC_COLLATE="C"
            LC_MONETARY="C"
            LC_MESSAGES="C"
            LC_ALL=


            $ locale -a
            POSIX
            en_US.UTF-8
            C
            iso_8859_1
            common


            In that case, what should I change my NLS_LANG to? to "C"? Or can change my locale to match with NLS_LANG - if yes, how?

            Sorry I don't have much knowledge about the locale settings. Please advise.

            Thanks in advance.

            Prashant.
            • 3. Re: Character discrepancy issue while spooling data
              Zoltan Kecskemethy
              Hi,

              I suggest to use UTF8.

              to change your locale to UTF8 issue in your terminal
              export LC_ALL=en_US.UTF-8
              so you can see if you could change it by
              locale
              and please use
              export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
              so this way your output files would be converted by oracle to UTF8 text!

              You need a support contract or partnership with oracle to able to reach my oracle support.

              Edited by: Zoltan Kecskemethy on Sep 18, 2012 5:47 AM
              • 4. Re: Character discrepancy issue while spooling data
                962612
                Hi Zoltan,

                Thanksfor your help again.

                I'm unable to set LC_ALL and NLS_LANG as suggested by you, getting the below error while executing my shell file:

                $ export LC_ALL=en_US.UTF-8
                LC_ALL=en_US.UTF-8: is not an identifier
                $ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
                NLS_LANG=AMERICAN_AMERICA.AL32UTF8: is not an identifier

                I tried using this:

                $ LC_ALL='en_US.UTF-8'; export LC_ALL
                $ NLS_LANG='AMERICAN_AMERICA.AL32UTF8'; export NLS_LANG

                This has given me no ERROR in shell file and both paramters changed indeed:

                $ locale
                LANG=
                LC_CTYPE="en_US.UTF-8"
                LC_NUMERIC="en_US.UTF-8"
                LC_TIME="en_US.UTF-8"
                LC_COLLATE="en_US.UTF-8"
                LC_MONETARY="en_US.UTF-8"
                LC_MESSAGES="en_US.UTF-8"
                LC_ALL=en_US.UTF-8

                $ echo $NLS_LANG
                AMERICAN_AMERICA.AL32UTF8

                But my script did not run successfully and the log says:

                ERROR:
                ORA-12705: invalid or unknown NLS parameter value specified

                It seems the NLS paramater AL32UTF8 is not supported by my enviroment.

                Please advise.
                • 5. Re: Character discrepancy issue while spooling data
                  Zoltan Kecskemethy
                  Yes you right in env setup. Sorry I gave you Linux(bash) syntax...

                  What is you DB version?

                  Please try to setup
                  NLS_LANG='AMERICAN_AMERICA.UTF8'; export NLS_LANG
                  • 6. Re: Character discrepancy issue while spooling data
                    962612
                    Hi,

                    We are working on oracle 8i.

                    I followed you steps and executed my script, problem seems to be unresolved.

                    For example : "SADETTÝN" is getting converted to "SADETTÃ N"

                    For you info, I'm able to see "SADETT/335N" in vi editor but "SADETTŸN" via notepad after I pcput the file to my disk. After digging further, I came to know - /335 is octal value for 'Ý'.

                    Please advise if there any other way this could be avoided.

                    Thanks
                    Prashant
                    • 7. Re: Character discrepancy issue while spooling data
                      Zoltan Kecskemethy
                      The issue is here that you don't have other available locale installed in your Unix system other than UTF8 to display your data well.
                      I think you must use this or install a new one.

                      So yes. /335 is UTF but your vi editor does not able to translate that.
                      You reported that you can see the UTF output in your desktop? And the values are as expected.
                      So you have a solution here as I see. I don't really understand what is the problem.
                      You can use a text editor (like notepad++) or any other tools to convert it to your needs...

                      Or you can try to find iconv for your unix version and convert it on the server locally!
                      • 8. Re: Character discrepancy issue while spooling data
                        962612
                        Hi,

                        Thanks for your suggestions.

                        I'm able to see the expected output after I use ftp client to transfer file to my local disk, rather than pcput command.

                        Prashant
                        • 9. Re: Character discrepancy issue while spooling data
                          Zoltan Kecskemethy
                          You are welcome. I'm glad that could help. Take care.