Monday, July 20, 2009

Character set conversion was done without using the csscan to verify if its possible.ORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-553: character set name is not recognizedCharacter set was converted from WE8ISO8859P1 to AL32UTF8.Helpful metalink note: 286964.1

Below query is used to see if there is a mismatch in character set in the database.SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'), 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;

So we can see that the above query also returns the old character set along with the new character set. It should ideally not show any record of the old character.Here you can see that there are 2 different character sets returned for VARCHAR2, CLOB & CHAR data, which indicates a mixup in the database character set setup, which is the cause for this PLS-553 error.

I had to work on this problem as in a new development database, someone accidently changed the charaterset and then we started getting all sorts of issues. Now lets find out which tables & columns are having problems:For CHAR,VARCHAR2 and CLOB columns: I have mentioned the old or problem character set in the below query, you are replace it with the one you are facing problem with.SQL> set lines 199SQL> col owner format a10SQL> col table_name format a20SQL> col column_name format a20SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1')order by OWNER, TABLE_NAME, COLUMN_NAME; 2 3

Oracle note suggests that if the character set conversion has happened between a 7/8 bit character set like WE8ISO8859P1, US7ASCII etc to a mutibyte character set like UTF8, AL32UTF8 etc, then there will be data loss for clob columns which display the old character set. So it is best to take a full back of the database, preferably using the tradional export utility.

SQL> select OWNER, TABLE_NAME, COLUMN_NAME from DBA_TAB_COLS where COLUMN_ID in (select unique COL# from sys.col$ where CHARSETID=NLS_CHARSET_ID('WE8ISO8859P1') and charsetform ='1') order by OWNER, TABLE_NAME, COLUMN_NAME;

no rows selected

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10;System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=2;System altered.

This procedure doesn't fix any data, it cannot get back the lost data due to character set changes. That will need to be restored from backup.All it does is fixes the references the tables are having to the old character set. Hence this is just the solution to fix the data dictionary not the data, or the loss that has happened.