Character set Conversion [message #668451] |
Mon, 26 February 2018 03:20 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi ,
As of now our existing Production CHARACTER SET was EE8MSWIN1250.Now our DBA team is planning to migrate it to new character set to AL32UTF8 .
There is a table Transaction_MSG tables with MSG1,MSG2,MSG3,MSG4,MSG5 columns with data type as
VARCHR2(4000 CHAR).
We have a message template which contains POLISH , SPECIAL, Our normal characters. while inserting into the table, the total message was splitting into multiple messages by calculating the length.
The first 4000 characters into MSG1, next 4000 characters into MSG2 and so on...
But in the new character set for those polish characters it considering two bytes.That's why even though length
is 4000, its unable to adjust into MSG1 column.
Here we don't know how many polish characters the total message it may contain.
Is there any way to find out upto how much length it can accommodate into MSG1 including polish characters;
If required we will go for one more column like MSG6 also.But how can I split the total messge string based on bytes it occupies.
Following is out put from before and after migrating the character set
OLD Character set
select * from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET EE8MSWIN1250
select length('Ą') length_in_characters, lengthb('Ą') length_in_bytes from dual;
LENGTH_IN_CHARACTERS LENGTH_IN_BYTES
--------------------------------------- ---------------------------------------
1 1
NEW Character set
select * from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET AL32UTF8
select length('Ą') length_in_characters, lengthb('Ą') length_in_bytes from dual;
LENGTH_IN_CHARACTERS LENGTH_IN_BYTES
--------------------------------------- ---------------------------------------
1 2
Thanks
SaiPradyumn
|
|
|
Re: Character set Conversion [message #668452 is a reply to message #668451] |
Mon, 26 February 2018 03:25 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This is going to be a horrible task. If I were your DBA, I would use this as a motivator for the upgrade to 12.x. If you do that first and enable extended strings, the character set change will be astronomically simpler. Discuss it with your DBA.
|
|
|
|