I got a strange error “[Oracle JDBC Driver]Transliteration failed, reason: invalid UTF8 data” while working on a client system. I spent a reasonable amount of time trying to work out what caused this.
The Oracle database was a restore of an Oracle 8.1.6 system onto the new Oracle XE 11.2. During the import, the character sets changed.
- export client uses WE8ISO8859P1 character set (possible charset conversion)
- export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
- import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
- import server uses AL32UTF8 character set (possible charset conversion)
So, I’m guessing since the new database did a conversion of NCHARSET from WE8ISO8859P1 to AL16UTF16, the size of the characters threw off something. Thus, there were problems and none of the queries on certain tables worked.
The Solution
The DataDirect Oracle Driver that ships with ColdFusion 9 has an error in it. It appears the error is fixed and if you have an agreement with the provider, you can download an update.
However, I don’t have an agreement so I downloaded fresh Oracle JDBC Drivers to fix the problem. Here’s what I did:
- Download the drivers here: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
- I used the ojdbc6.jar one.
- Copy the ojdbc6.jar file to /JRun4/lib (or, if you wanna be fancy, put it somewhere else and update the class path in the jvm.config pertaining to the instance you want to update)
- Restart ColdFusion
- Enter the following in the JDBC URL field: jdbc:oracle:thin:username/password@IP.Address.Of.Database.Server:PortOfDatabaseServer:OracleSID
- Enter the following in the Driver Class field: oracle.jdbc.driver.OracleDriver
- Add the user name and password in the appropriate boxes
- Save the datasource. It should verify if you did everything correctly.
If you got an error, remember these things:
- Usernames, passwords and seemingly the Oracle SID are case sensitive
- The JDBC Url Field is particular and must be exactly right.
- The default port for Oracle is 1521