After upgrading one of our testing environments to a newer oracle version, some of our services started to return the following error:
SQLException: Unsupported character set (add orai18n.jar to your classpath)
From the error message wording, one could suspect that this is an character set but after running
SELECT VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';
Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET';
We found out that both databases return the same results.
Therefore, it is obvious that there is a version mismatch between the client and the server. And this message is Oracle’s way of telling you that you need to upgrade your client. Since we are using WebLogic, the process is more complicated.
As suggested, we tried adding the orai18n.jar to the classpath but to no avail.
In addition we discovered that all the problematic service select xmltype from the Database so we assumed that this issue could be solved by some kind of encoding workaround.
We tried converting the character set (convert(col1,’IW8ISO8859P8′,’UTF-8′); ) or tweaking the NLS settings (alter session set nls_territory) only to discover that the simple solution is to convert the xmltype to string and reconverting it back to xmltype
SELECT XMLELEMENT("RESULTS", XMLELEMENT( "Field",XMLATTRIBUTES(col_name_1 AS "name") ,col_value_1 ), XMLELEMENT( "Field",XMLATTRIBUTES(col_name_2 AS "name") ,col_value_2 ) ) INTO xml -- xmltype FROM (SELECT 'NAME1' AS col_name_1 ,'VALUE1' AS col_value_1 ,'NAME2' AS col_name_2 ,'VALUE2' AS col_value_2 FROM dual ); RETURN xmltype(to_clob(xml.getStringVal()));
select XMLELEMENT("RESULTS", XMLELEMENT( "Field",XMLATTRIBUTES(col_name_1 as "name") ,col_value_1 ), XMLELEMENT( "Field",XMLATTRIBUTES(col_name_2 as "name") ,col_value_2 ) ) into xml -- xmltype from (select 'NAME1' as col_name_1 ,'VALUE1' as col_value_1 ,'NAME2' as col_name_2 ,'VALUE2' as col_value_2 from dual ); return xmltype(to_clob(xml.getStringVal()));