Fix Charset Issues in SQL*PLUS

oracle

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 *,
Case
        When Teste_CHAR=Teste_CHAR_ASCII
        Then 'OK'
        Else 'Erro'
END as STATUS_TESTE,
Case
       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?

Best Answer

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

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    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

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage
    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
Related Question