7 Replies Latest reply: Nov 8, 2007 3:08 PM by plhtyuk RSS

    sqlplus input script and codepage on Windows

    plhtyuk
      Hello Everyone,

      I read this interesting article on Metalink:

      https://metalink.oracle.com/metalink/plsql/f?p=130:14:6029680182771240170::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,179133.1,1,1,1,helvetica

      But it does not answer my questions.

      How exactly does sqlplus behave on Windows regarding the codepages of the files it executes?

      I made some tests and it seem that it always insert the correct data when my file is save in 1252 codepage regardless of the NLS_LANG value and also regardless of the console codepage (DOS).

      Can someone shed some light on this?

      Best regards.

      Carl

      Message was edited by:
      plhtyuk
        • 1. Re: sqlplus input script and codepage on Windows
          Laurent Schneider
          well, there is a conversion problem from the server codepage (probably 1252 in your case) and the client codepage (something like WE8PC850 if you use MS-Dos console).

          What is your client character set and your server character set? What is the problematic character? What is the output of
          select 'é', ascii('é') from dual;
          • 2. Re: sqlplus input script and codepage on Windows
            plhtyuk
            Hello Laurent,

            The characterset of the Oracle server is: WE8ISO8859P1

            The characterset of the Oracle client is: WE8MSWIN1252

            Here is the output:
            SQL> select 'é', ascii('é') from dual;
            
            ' ASCII('┐')
            - ----------
            ┐        191
            Best regards,

            Carl
            • 3. Re: sqlplus input script and codepage on Windows
              Laurent Schneider
              what is the client ? the dos console?
              • 4. Re: sqlplus input script and codepage on Windows
                orafad
                I made some tests and it seem that it always insert
                the correct data when my file is save in 1252
                codepage regardless of the NLS_LANG value and also
                regardless of the console codepage (DOS).
                What settings and code pages do you use, what gets stored in the db, and what output do you get?


                Open notepad, enter åäö or some other non-ascii repertoire characters, then save to c:\aao.txt. Now open a console mode prompt (dos box) and use type/edit c:\aao.txt. Does the characters look ok? (here, Õõ÷ comes out, code points E5 E4 F6, which happens to represents åäö in windows-1252!)

                Ok, this was step one.

                Having correctly set nls_lang=.we8pc850 and inserted these characters, I end up having stored D5 F5 F7, representing Õõ÷ in iso 8859-1.

                That was step two.

                Now, finally, I open up GUI SQL*Plus, with char set part of nls_lang set (automatically in the registry) to we8mswin1252 - this matches current ACP in Windows - and type in
                select column,dump(column,1016) from table where ...;
                This displays Õõ÷ and not åäö.

                Note the conversions client - server - client.

                Hope that helps.
                • 5. Re: sqlplus input script and codepage on Windows
                  orafad
                  The characterset of the Oracle server is:
                  WE8ISO8859P1
                  Btw, having windows-1252 clients and a server with WE8ISO8859P1 might not be a working setup. (Since 1252 defines several characters, like the "smart" quotes, that does not exist in iso 8859-1!)
                  • 6. Re: sqlplus input script and codepage on Windows
                    plhtyuk
                    Hello Laurent et Orafad,

                    I finally found what I was doing wrong: I was using SQLPLUSW to read the data and SQLPLUSW was opened before the change of the NLS_LANG value.

                    My next concern now is how can I get the current characterset of an Oracle client being inside an executable?

                    Do I have to find the path of sqlplus.exe, find the Oracle.key file, open it read it and finally read the proper value in the registry?

                    Is there a simpler way to do that?

                    We want to know the characterset of the current Oracle client in order to run our conversion script into the characterset that sqlplus expects.

                    Best regards.

                    Carl
                    • 7. Re: sqlplus input script and codepage on Windows
                      plhtyuk
                      Should we use: OCINlsGetInfo() ?

                      Does it return the characterSet of the Oracle client?

                      Best regards.

                      Carl