CODEWITHSUNDEEP
oracle-databaseplsqlutf-8character-encodingoracle12c
Shaun Kinnair
Shaun Kinnair

Reputation: 535

Convert Oracle 12c database from WE8MSWIN1252 to utf-8 character set

I've been tasked with giving my project manager development/test estimates. We are converting our Oracle12c database from a character set of WE8MSWIN1252 to utf-8 characters.

I've been tasked to come up with development change estimates on the following.

  1. Switch datatype from Byte to Char (i.e. VARCHAR2(10 byte) to VARCHAR2(10 char) )
  2. changing code where Length needs to be replaced by lengthb

My Questions.

  1. Do I only have to worry about changing database table columns from byte to char? or are there other objects on the database I might need to look at?
  2. Is Length to Lengthb the only Pl/SQL function I need to look at?
  3. As a developer is there any other areas I need to look at (i.e. We have Oracle forms/reports)?
  4. I noticed the below code can Alter all the tables columns in user_tab_columns do I need to run this for other object types and objects that are not fetched by the below query.
   BEGIN
    FOR X IN (SELECT C.TABLE_NAME, 
                     C.COLUMN_NAME,
                     C.DATA_TYPE,
                     C.DATA_LENGTH
                FROM USER_TAB_COLUMNS C
               WHERE DATA_TYPE LIKE 'VARCHAR%' 
                AND CHAR_USED = 'B'
                AND NOT EXISTS (SELECT 1 FROM USER_VIEWS V WHERE V.VIEW_NAME = C.TABLE_NAME))
    LOOP
        EXECUTE IMMEDIATE('ALTER TABLE '||X.TABLE_NAME||' MODIFY '||X.COLUMN_NAME||' '||X.DATA_TYPE||'('||X.DATA_LENGTH||' CHAR)');
    END LOOP;
    END;

Thanks in advance guys.

Upvotes: 0

Views: 3389

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11586

I would strongly suggest you take a read of note 225912.1 on support.oracle.com.

There may be a fair bit involved in changing the characterset, including scanning for existing data that might need to reloaded/altered etc.

Upvotes: 1

Related Questions