I have Oracle 12.2 server on a Linux box.
On the server box, I did the followings in sqlplus:
So everything looks good, I think the reason that US7ASCII can display the name with accents is because both server and the client (sqlplus) use US7ASCII, no conversion was done.
But when I move to Toad (version 13.3 on a Windows 10 laptop), same select gave me:
SAYING
agr�cola - Spanish word for agricultural
universit� - Italian word for university
Sall� - a 19th century French taxonomist
S�o Paulo - Portuguese, Brazil's largest city
Adam B�ving - a Danish entomologist who worked in the USA
What I need to do make Toad display the same thing sqlplus was showing?
Thank you very much!
I am in USA, Windows 10 was installed by specifying US, USA, English...
If I check regedit, under Oracle client home:
it says: NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
Now check Toad:
First of all, when you created the table, knowing you were inserting accented characters, you should have used NVARCHAR2.
US7ASCII is a throwback to less enlightened times. Unless your database is guaranteed never to use accents, it is of little use. I can't remember when I last created a database with that characterset!
Try setting your characterset in your Toad session:
Alter session set nls_characterset = 'US7ASCII'; then run your query again. Do you see accents?
If not, change the font used in the grid to one that can display accents. "Courier New" is what I used in the past.
HTH
Cheers,
Norm.
Thank you Norm for the suggestion.
Our database was first created more than 20 years ago, apparently US7ASCII was the natural choice back then, and all tables were created using VARCHAR2. Just a few months ago we have new business requirement to support Western European languages. Of course we would like have minimum changes around. So this POC table was created with VARCHAR2 and not NVARCHAR2 on purpose.
I have read many online blogs that if somehow we make server and the client both US7ASCII, then Oracle would NOT do any conversion, and treats the data as is (bit by bit). This actually worked as demonstrated by the sqlplus session on the server.
When I issued: Alter session set nls_characterset = 'US7ASCII'; I got ORA-00922: missing or invalid option.
The data grid of Toad is already using "Courier New". see screenshot below:
Please advise... Thank you!
Aha! Yes, back then you didn't have NVARCHAR2 -- I remember it well.
I suspect you will have to rebuild the table at some point to use the correct characterset.
It is true that when the session and database have the same characterset that no conversion is done. I've suffered from this in the past with access to the database and also when using exp/expdp to export data. Most irritating.
The ORA-00922 error is a pain! I think in that case you might need to change the setting in the registry as per : Setting the NLS_LANG Environment Variable for Oracle Databases.
I suffered from a similar problem back in the late 1990s when the company I worked for started having European customers with accented characters.
This was Oracle 7.3.4 and we ended up having to follow the procedures to change our database characterset, which luckily worked or we would have had to export/drop database/rebuild/import.
HTH
Cheers,
Norm.
Thank you Norm, It seems changing characterset for the DB is the only path.
Happy New Year!
Happy New Year to you too. Let's hope 2021 is much, much better than 2020!
Cheers,
Norm. [TeamT]
