3

I have a problem when inserting values into my Oracle database. I have to insert French characters like à or è and when I try to insert them through an INSERT statement it will convert the character to ¿ or ?.

Is there any possibility to set the encoding of that specific script, or what can I do in this situation ?

Thank you

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sebastian
  • 617
  • 2
  • 10
  • 30
  • 4
    Check the CLIENT NLS_LANG SETTINGS, make sure it is set to retrieve french characters. – Haytem BrB Jan 25 '16 at 13:42
  • @haytem - it is set to American_America.WE8ISO8859P1. The thing is that when the client inserts the values, it inserts them properly, but when we do it, from the development environment, the values are encoded – Sebastian Jan 25 '16 at 14:48
  • You can use the following query to get all NLS parameters: SELECT * FROM nls_database_parameters The problem you have is due to different settings between client and server (In PL/SQL Developer Help->About, Additional Info button and scroll down) – Haytem BrB Jan 25 '16 at 16:18
  • Wrong characters may be displayed as a result of many (mis)configuration factors. Please review [this answer](https://stackoverflow.com/questions/18120723/how-to-insert-indian-rupee-symbol-in-oracle-11g/18198563#18198563) and check settings. – ThinkJet Jan 25 '16 at 16:27
  • Do you enter the data manually in SQL Developer or do you run an `.sql` file which has been created with another tool? – Wernfried Domscheit Jan 25 '16 at 21:36

2 Answers2

1

Usually you would set the character set when you install your database. You can, however, change it post-setup if required (Look up CSALTER). If your database needs to support multiple languages, then you should take a look at this: Supporting Multilingual Databases with Unicode

gmiley
  • 6,531
  • 1
  • 13
  • 25
1

I have fixed this problem by adding an Environment Variable called NLS_LANG with the value .AL32UTF8 . This worked even though the database has as language American and territory America. The problem that I have faced here was that once I changed the NLS_LANG variable, it started to encode my characters also in the application.

Also you can try to change the encoding of the script that you are running. For example I have used ANSI encoding (you can do it by opening a script in notepad++ and from the Encoding menu, select Convert to ANSI) and it worked properly.

Thank you guys for your help :)

Sebastian
  • 617
  • 2
  • 10
  • 30
  • 1
    The language and territory has **nothing** to do with the ability which characters can be stored in the database nor which characters can be displayed by your client application. Language and territory are more related to names of weekdays or month, rules for sorting, calendar settings, language of error messages, etc. – Wernfried Domscheit Jan 26 '16 at 07:52