My Oracle Support Banner

How to Diagnose Losing Characters , Getting "Funny" Output When Inserting or Selecting Other Than A-Z,a-z ( = Non English Data Like Chinese, Russian, Hebrew , Insert Any Language Here to the List That is Not English) in/from CHAR, VARCHAR2, LONG or CLOB (Doc ID 1628060.1)

Last updated on DECEMBER 30, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 and later
Oracle Database - Standard Edition - Version 9.0.1.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 10-Oct-2016***

NOTE: In the images and/or the document content below, the user information and environment data used
represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered
with an Oracle database product or other training material. Any similarity to actual environments,
actual persons, living or dead, is purely coincidental and not intended in any manner.

Symptoms

Trying to select from or insert in CHAR, VARCHAR2, LONG or CLOB datatypes other than A-Z,a-z data ( = non English data like Chinese, French, Russian, Hebrew , insert any language here to the list that is not English or "special characters" like the € symbol) and the result is

 

For problems trying to insert "special" characters in Nchar, Nvarchar2 or Nclob (note the N) please see <note 276914.1> The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g , 11g and 12c /  13)  I'm inserting <special character> in a Nchar or Nvarchar2 col but  it comes back as ?, ¿ or without accents when selected.

 

Changes

Changes can be many, new client installation, new database installation or simply trying to use a not used language before in an existing system with "normal" CHAR, VARCHAR2, LONG or CLOB column types.
This note provides a step by step approach to find out where the problem is and then how to correct it.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
 A) Install Oracle SQLdeveloper 4 (or 3)  on an windows client (preferably Microsoft Windows 7 or higher)
 B) Confirm this Microsoft Windows client used for Oracle Sqldeveloper can display the language you want to check/see and has the needed OS support and fonts.
 B.1) check if there is need to add support to Microsoft Windows Operating System for the language(s) that are used .
 B.2) check if the Microsoft Windows has Unicode fonts installed that define the language(s) that are used .
 C) If point B) is fine then confirm that the Oracle SQLdeveloper interface can handle the language(s) you want to check.
 D) If point C) is fine then use Oracle SQLdeveloper to check that the database is able to store the characters/language you want to use.
 E) If point D) is fine then use Oracle SQLdeveloper to check EXISTING data inserted or loaded trough an application, script , batch job, import, sqlldr, external table, dblink from an other database, etc.
 F) If point E) is fine then check the configuration of the selecting tool / application.
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.