It works after adding convertNcharLiterals=true; in sqldeveloper.conf
Now I want to perform the same thing from a ASP.NET web application that uses OLEDB connection to Oracle database.
is there any way to add the above property(convertNcharLiterals) when querying the Oracle database from OLEDB connection.
I am using OraOLEDE.Oracle provider to connect to a Oracle 11g Database with database character set WE8MSWIN1252.
Now I am able to Insert and retrieve Unicode data in the correct format after setting the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE on the Application Server where the ASP.NET website is hosted.
BUT Now the issue that I am facing is:
I have an existing Oracle 11g database again with character set WE8MSWIN1252 which has several tables with datatypes CHAR, VARCHAR2, NUMBER and BLOB.
I want to be able to save Unicode data in all the VARCHAR2 and CHAR columns
Please suggest some approaches to do that.
What I can think of is to convert all CHARS to NCHAR and VARCHAR2 to NVARCHAR2, but how to change the data type of all the columns without affecting the
1. Data in them
2. The constraints applied on them (if any).
3. Indexes applied on them (if any).
Thank you for your suggested approach of changing the database character set to AL32UTF8.
But I have a question regarding it. Lets say, if I migrate the Character Set Using the CSALTER Script how would it affect the size of CHAR and VARCHAR2 data type columns?
The NLS_LENGTH_SEMANTICS is BYTE
There is table Sample with column col1 CHAR(10)
So, according to NLS_LENGTH_SEMANTICS the column col1 can store 10 Bytes ('ABCDEFGHIJ' = 10 Bytes)
Now, If I change the Database character set to AL32UTF8 and then try saving Chinese characters.
When I will try saving 语语语语语 (5 Chinese characters = 5 * 3 = 15 Bytes ) would there be any data loss or truncation? (if yes then is there any solution for this?)
The default (and recommended) value for NLS_LENGTH_SEMANTICS is BYTE. If you have a column defined as CHAR(10) or VARCHAR2(10), then it can store 10 bytes of data. Some characters in AL32UTF8 (and other multibyte charactersets) are stored/represented using multiple bytes (up to 3). Converting to AL32UTF8 (and other multibyte charactersets) can cause truncation of data if data will take up more than the column width (10 bytes in this case).
See explanation here - http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#i1005945
Use the CSSCAN utility to determine if such truncation will occur before using CSALTER.
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) [ID 745809.1]
You could also use the Migration Assistant for Unicode - http://www.oracle.com/technetwork/products/globalization/dmu/overview/index.html - to scan the database
There is one more scenario I am not able to get through and it would be a great help if you provide any suggestion on this:-
1) NLS_CHARACTERSET: WE8MSWIN1252
2) Column Data types: CHAR, VARCHAR2 etc.
3) All CHAR and VARCHAR2 columns have ONLY English alphabet letters (a-z A-Z)
For e.g. Users table with username column of type VARCHAR2(20) which directly maps to a textbox on a webpage which says "Enter user name (max 20 chars): "
Migration of Database Character Set
1) I run the CSSCAN utility to check for any truncation or data loss in migrating to AL32UTF8.
2) CSSCAN reports no errors and then I use CSALTER which runs successfully.
3) NLS_CHARACTERSET: AL32UTF8
Thus the Database Character set is migrated to AL32UTF8.
Using the Migrated Database
1) Now the database supports Unicode data which can be saved in CHAR and VARCHAR2 data types.
2) Lets come to the Users table example:
Lets say an end user opens the web page which says "Enter user name (max 20 chars): " and the user enters 20 Chinese characters (or any 20 double byte or triple byte characters.)
This would try saving more than 20 bytes in a 20 byte field (username VARCHAR2(20))
which would result in an error.
So, Should I be thinking of altering all CHAR and VARCHAR2 columns to NCHAR and NVARCHAR2 columns? (if yes then how ?)
Yes - your approach will work - there is no easy/simple way to do this. You have to evaluate which columns will need to be expanded - I have not found that thread yet, so hopefully Sergiusz can explain again why NCHAR and NVARCHAR2 datatypes should not be used.