0

I am working with an Oracle 12.2 database. The database characterset is WE8MSWIN1252 (ie. an ASCII characterset).

The database contains a table with a CLOB column (according to Oracle SQL Developer). Some values in this column contain non-ASCII characters (I know this as when using ASCIISTR function on this column I can see the escaped non-ASCII character codes).

How is this possible? I thought ASCII characterset databases could only store unicode in NVARCHAR, NCLOB etc.

(I only discovered this when I was using a linked server to the Oracle db from SQL Server - when I ran an OPENQUERY on the table with the CLOB, it returned ? for the non-ASCII characters. I changed the OPENQUERY query string to use TO_NCLOB(clob_column) and it returned the non-ASCII characters.)

Any ideas?

Thanks

Stuart King
  • 1
  • 3
    "ASCII" is only byte values 0-127. 128-255 are used by various other legacy character sets compatible with ASCII. Given the name WE8MSWIN1252 I'd refer to [Windows-1252](https://en.wikipedia.org/wiki/Windows-1252) for what the upper values represent in that database. – Mark Tolonen Dec 15 '22 at 07:15
  • Character set WE8MSWIN1252 is not a ASCII character set. However, it is possible to have invalid characters in the database, see https://stackoverflow.com/questions/47039666/if-we-have-us7ascii-characterset-why-does-it-let-us-store-non-ascii-characters/47051107#47051107 – Wernfried Domscheit Dec 15 '22 at 14:47

1 Answers1

0

From wikipedia WE8MSWIN1252 description: Windows-1252 or CP-1252 (code page 1252) is a single-byte character encoding of the Latin alphabet, used by default in the legacy components of Microsoft Windows for English and many European languages including Spanish, French, and German.

So, it a CLOB in a database with this charset can store strings like éàè. And ASCIISTR returns escaped codes because these chars are not defined in ASCII, for example:

SQL> select asciistr('é') eaccent, asciistr('e') e from dual;

EACCENT    E
---------- -
\FFFD\FFFD e
gsalem
  • 1,957
  • 1
  • 8
  • 7