We are creating scripts to sent to our customers, with some inserts. We save them with ANSI encoding on NOTEPAD++, but when the customer runs the script via SQL*PLUS, it looks like this:

enter image description here

How can I be sure that the customer will run the script, with ansi encode, so we have no problems with those symbols on â, ó and et.

We told them to customize their environment to use NSL_LANG = WE8MSWIN1252. TEST QUERY:

Select *,
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
       When Teste_CHAR=Teste_CHAR_ASCII
       Then 'Everything is OK'
       Else 'Script will not run. your encode is different from ours'
END as Mensagem_TESTE
 from (
Select CHAR(ASCII('ã')) As Teste_CHAR, CHAR(227) as Teste_CHAR_ASCII
) A

as said here ( **How to be sure that a SQL Script is running with ANSI encoding? ) **this is not correct. But, is there a way to use something like this?


First problem is: What do you mean by ANSI?

ANSI is a common misnomer for your default Windows character set. It depends on your Windows language. See a list of codepages on National Language Support (NLS) API Reference (column "ANSI codepage")

You can also query your Registry to get it:

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v ACP

    ACP    REG_SZ    1252

Let's assume it is CP1252, the most common on "Western" PC's

When you run SQL*Plus then it inherits the codepage from your cme.exe. However this is not "ANSI codepage", you get it in column "OEM codepage" or by Registry

C:\>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage /v OEMCP

    OEMCP    REG_SZ    850

This is typically CP850 (for Western Europe) or CP437 (in US)

You can change and interrogate the code page by command chcp

NLS_LANG parameter tells the database which character set you are using (because the Oracle Database cannot interrogate your settings from Notepad++ or cmd.exe)

So, it order to make it working run following:

C:\>chcp 1252
Active code page: 1252

C:\>set NLS_LANG=.WE8MSWIN1252

C:\>sqlplus user/pwd@DB @xyz.sql
| improve this answer | |
  • Thank you very much, BUt just one question that I can't find the answer on google. Why oracle uses OEMCP ? I could run ACP on my machine, and I got 1252. but running OEMCP, I got 850. I didn't understand what this means. by the way I'm from Brazil. – Racer SQL May 23 '18 at 12:49
  • Sorry...Why SQL*PLUS use OEMCP codepage? – Racer SQL May 23 '18 at 12:54
  • 1
    SQL*Plus inherits the code page (or name it "character set" if you prefer the term) from calling command-line (this also applies for Linux/Unix). The problem is Microsoft Windows which by default uses on command-line (i.e. cmd.exe) a different code page than on GUI applications (where it is typically called just "ANSI"). I found this blog: archives.miloush.net/michkap/archive/2005/02/08/369197.html regarding this topic. – Wernfried Domscheit May 23 '18 at 12:58
  • 1
    In earlier times there was an application sqlplusw.exe GUI based, i.e. that one used ACP. But it does not exist anymore in current Oracle releases. – Wernfried Domscheit May 23 '18 at 13:08
  • 1
    Yes, should be the same – Wernfried Domscheit May 24 '18 at 13:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.