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?

There must be something else going on because US7ASCII is a 7-bit encoding that doesn't support 'DEGREE SIGN' (U+00B0) so you should be getting a literal ? question mark on return (or maybe another replacement character). You can google for Globalization Support Guide, which explains this pretty well. Strange. — Álvaro González
Character Set Conversion in a Monolingual Scenario: "When a target character set does not contain all of the characters in the source data, replacement characters are used. If, for example, a server uses US7ASCII and a German client uses WE8ISO8859P1, then the German character ß is replaced with ? and ä is replaced with a." — Álvaro González
When viewed through Oracle SQL developer, it appears as an upright rectangle character. When viewed through Aqua Data studio, it appears as a degrees symbol. — Matthew Walk

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2017-11-01T08:47:38

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.

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

2 Answers