Oracle and WebSphere — an unholy pair
So we are having problems with WebSphere and Oracle. Here’s the setup:
- Oracle 10g running on Windows
- Oracle 9i running on Linux
- WebSphere 5.1 running on Linux
- using the Oracle thin JDBC driver
When we connect WebSphere and the 10g together things work magically well. When we get WebSphere and 9i to talk, we get this error: ORA-12704: character set mismatch. The NLSNCHARCHARACTERSET and NLS_CHARACTERSET are the same on the 10g and 9i servers. This pages gives a lot of info, but not enough to tell us what to do. The 9i works on JBoss and Weblogic, so it’s something to do with WebSphere on Linux, but we don’t really know what.
My theory, which stems from the link above, is that when WebSphere connects, the JDBC driver doesn’t know what characterset to send to Oracle, so it defaults to US7ASCII(which is 7 bit) when talking to 9i and UTF8 (which is 8-bit) when talking to 10g. Well, we sent Unicode characters to Oracle, and 9i blows up, because Unicode characters are at least 8-bit. But setting NLS_LANG on the WebSphere server doesn’t seem to help, so maybe this theory is bad.
Anyone else in the land of LazyWeb have any ideas?
Update: We tried it on JBoss on WinXP and got the same result — worked on the 10g box and not on the 9i box. So I deleted the database and created a new one, using UTF8 for both the character set and the national character set. Guess what? Same result! So we have opened a case with Oracle. Our theory is that the Oracle version isn’t as important as the OS that Oracle is running on. So Oracle on XP can handle our stuff better than Oracle on Linux.
Update 2: After some testing, we figured out what is happening. We are trying to insert a null into an NCLOB column. For some reason, Oracle 9i stubbornly refuses to do this, while Oracle 10g happily rolls along, just like SQLServer, DB2, and Sybase (yes we must support all those database types. Yes, we are insane). We can even recreate it outside of an app server. Yippee! But Oracle support still doesn’t have a work-around for us besides use Oracle-specific JDBC call. Sorry, can’t do that, because we are insane.