1

I have data which contains special characters like à ç è etc..

I am trying to insert the data into tables having these characters. Data gets inserted without any issues but these characters are replaced with with ?/?? when stored in tables

How should I resolve this issue?I want to store these characters in my tables.

Is it related to NLS parameters? Currently the NLS characterset is having AL32UTF8 as seen from V$Nls_parameters table.

Is there any specific table/column to be checked ? Or is it something at the database settings ?

Kindly advise.

Thank in advance

sqlpractice
  • 141
  • 1
  • 3
  • 11
  • DataType for the column must be NVARCHAR – Jaydip Jadhav Mar 23 '16 at 13:46
  • @Jaydipj: I don't believe that's accurate. I've had VARCHAR2 fields defined and working with those characters with no issue before. It's about the character set and defined NLS_LANG, not your data type ... – Ditto Mar 23 '16 at 13:48
  • @sqlpractice: Show your NLS_LANG parameter from the client when doing the insert. – Ditto Mar 23 '16 at 13:49
  • It is Varchar2(50 char). I am able to see the characters in one database but not in other.Table specification is same in both the databases – sqlpractice Mar 23 '16 at 13:49
  • @sqlpractice: also, can you insert a single character: `à` .. and then `select col, dump(col) from yourtable;` ?? – Ditto Mar 23 '16 at 13:51
  • @Ditto when I tried select àctive from dual. It replace à with ? Also where can I see NLS_LANG ..I checked NLS_LANGUAGE it is American. LANG variable in export is defined as en_US.UTF-8 – sqlpractice Mar 23 '16 at 13:57
  • Where do you see these ?? ? I've tried to select data from a table using sqlplus from ms-dos command prompt on a French OS and I see the `à` correctly but doing the same thing from an English OS displays `ÔǪ` – StephaneM Mar 23 '16 at 14:07
  • @StephaneM I am using sqplus from putty.language is American. Both the database have same language but it is generating ? in one server only – sqlpractice Mar 23 '16 at 14:14

1 Answers1

1

From the comments: It is not required that column must be NVARCHAR (resp. NVARCHAR2), because your database character set is AL32UTF8 which supports any Unicode character.

Set your NLS_LANG variable to AMERICAN_AMERICA.AL32UTF8 before you launch your SQL*Plus. You may change the language and/or territory to your own preferences.

Ensure you select a font which is able to display the special characters.

Note, client character set AL32UTF8 is determined by your local LANG variable (i.e. en_US.UTF-8), not by the database character set.

Check also this answer for more information: OdbcConnection returning Chinese Characters as "?"

Community
  • 1
  • 1
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • thanks for your answer.So setting NLS_LANG would be session specific? I mean will I have to do this every time I want to see the data with a new sqlplus session? If yes, then is there any permanent solution.. Also how can we see the current NLS_LANG variable value – sqlpractice Mar 23 '16 at 14:47
  • `NLS_LANG` is set for your client, it is not session specific, i.e. you cannot change it inside a session. You can set your `NLS_LANG` environment variable permanently. In order to change the encoding of your `cmd.exe`, resp. SQL*Plus follow this answer: http://stackoverflow.com/questions/388490/unicode-characters-in-windows-command-line-how/33475373#33475373 – Wernfried Domscheit Mar 23 '16 at 14:56
  • I am connecting to the server using putty.exe and then connecting sqlplus on the server and I cannot change the registry on that server..Also how can I see the current value of NLS_LANG – sqlpractice Mar 23 '16 at 15:03
  • Check settings of Putty there you can set Utf8. Run "echo $NLS_LANG" – Wernfried Domscheit Mar 23 '16 at 18:47
  • My putty settings already have UTF-8 .I got nothing when I tried echo $NLS_LANG ..I got blank row even on the server which is storing the correct characters. – sqlpractice Mar 24 '16 at 04:28
  • Google for "how to set environment variable in Linux"! – Wernfried Domscheit Mar 24 '16 at 05:37
  • Thank you all for your replies.Issue is resolved now.The problem was with sqlplus settings.If I see the same data using SQLDeveloper the chars are visible as expected..It has something to do specific to SQLPLUS..please enlighten if anyone has more information on this as what specific settings ?why does it happen – sqlpractice Mar 24 '16 at 14:50