oracle - If we have US7ASCII characterset why does it let us store non-ascii characters?

Question

We have an Oracle database that has the NLS_CHARACTERSET = US7ASCII.

As a test, we ran an insert into a table that contains a VARCHAR(4000) field as were able to put a CHR(176) value in that column (degrees symbol).

That character doesn't appear to be supported by US7ASCII.

Why would the database allow that value to be stored in that column?

Answer 1

It works because following conditions are both true:

• Client character set is equal to your database character set.
• The character set permits any byte values

Your database character set and your client character set are set to US7ASCII. In such case each data is written/read one by one without any conversion, i.e. the bytes you send are exactly written to database. Probably you did not set NLS_LANG at all on your client side but Oracle defaults it to AMERICAN_AMERICA.US7ASCII.

US7ASCII is a 7-bit encoding. I assume a pure ASCII application (which could be fairly difficult to find) would just ignore the 8th bit which is stored in an 8-Bit architecture. Other character sets, e.g. AL32UTF8 do not allow each byte value. In this case such characters will be replaced by a placeholder, e.g. ? or ?.

Note, you set your client character set to US7ASCII which is most likely not correct. Set it properly to the character set which is used by your application, then ° will get replaced.

In case you use SQL*Plus check console codepage with command chcp, resp. locale charmap. Set your NLS_LANG environment variable accordingly before you start sqlplus.