DB version : 12.1.0.2
Platform : RHEL 7.2
This production DB was upgraded from 10.2 to 12.1.0.2 a month back. After this upgrade, occasionally, my Java application encounters the following error
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
When I contacted oracle support, they say it could be an error mentioned in MOS Doc : 286964.1
select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)
order by CHARACTERSET, TYPES_USED_IN;
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR -------------->
AL16UTF16 NVARCHAR2 -------------->
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 LONG
AL32UTF8 VARCHAR2
UTF8 NCHAR ------------>
UTF8 NCLOB
UTF8 NVARCHAR2 -------->
For NCHAR and NVARCHAR2 columns, 2 different character sets.ie. AL16UTF16 and UTF8 which is Wrong
--- Good output will be like below. For one datatype, you should have only one characterset
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 LONG
AL32UTF8 VARCHAR2
7 rows selected.
If the above mentioned is the real cause, why do I get this error only intermittently?
-- Following is the NLS setting of this DB
SQL> select * from nls_database_parameters;
PARAMETER VALUE
--------------------------------------------- ---------------------------------------------
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET UTF8
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
noncdb_compatible boolean FALSE
Potential Fix:
MOS Doc: 286964.1 is advising to run the following command afte backing up the DB. But, will it actually fix the issue ?
Has anyone encountered the same issue ?
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
Additional Info:
Info1.
Although my application encounters PLS-00553 occasionally, the application schema doesn't have a single table with N-Type column.
All N-Type columns are used by internal users like SYS,SDB, SYSTEM,...
SQL> select owner, count(*) from DBA_TAB_COLUMNS where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB') group by owner order by count(*) desc;
OWNER COUNT(*)
----------------------------------- ----------
SYS 98
APEX_040200 8
XDB 7
SYSTEM 3
Info2: NLS_LANG parameter is not set at client level. Latest JDBC drivers are used at the client side.