Oracle – Correctly Set UTF-8 Encoding

oracle

I check my db,and seems to be UTF8 complaint

select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER        |VALUE
NLS_CHARACTERSET |AL32UTF8

But when i do a search on a VIEW

SQL> select titolo,anno,genere from generale where titolo like  '%Roma dr%' order by titolo;

TITOLO                                                      |ANNO   |GENERE
------------------------------------------------------------|-------|------------
Roma drogata: la polizia non pu?? intervenire               |1975   |Poliziesco

1 row selected.

Elapsed: 00:00:00.02

The two ?? tell me something is wrong.
Any solution to give a full utf8?Thanks

Best Answer

Based on your reply of 

SELECT ASCIISTR(titolo) FROM generale WHERE titolo LIKE 'Roma drogata%;

TITOLO                                                      
----------------------------------------------------------------
Roma drogata: la polizia non pu\FFFD\FFFD intervenire

You get Roma drogata: la polizia non pu?? intervenire because it has been inserted wrong into your database, i.e. when the record was inserted the client had set NLS_LANG wrong.

Unicode character U+FFFD is this one: REPLACEMENT CHARACTER

Probably the SQL script was saved as UTF-8 but NLS_LANG was set to ISO-8859-1 or Windows-Cp1252 or similar.

Related Question