Home » SQL & PL/SQL » SQL & PL/SQL » Character set Conversion (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Character set Conversion [message #668451] Mon, 26 February 2018 03:20 Go to next message
saipradyumn
Messages: 365
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 Go to previous messageGo to next message
John Watson
Messages: 7671
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.
Re: Character set Conversion [message #668454 is a reply to message #668452] Mon, 26 February 2018 03:30 Go to previous message
saipradyumn
Messages: 365
Registered: October 2011
Location: Hyderabad
Senior Member
Hi

Thank you very much for valuable information.Will discuss with DBA Once .

SaiPradyumn
Previous Topic: Query help
Next Topic: Interval Clause in Date
Goto Forum:
  


Current Time: Mon Nov 19 15:39:54 CST 2018