This note is for java 1.6 (JDeveloper 11.1), the Oracle 10 database and weblogic 10.3.

I am inserting data into a database table with a column of type XmlType with java code:

import java.sql.Connection;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.sql.DataSource;
...
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(dsstr);
Connection conn = ds.getConnection();
String query = "begin INSERT into myxmls VALUES (?,?); commit; end;";
PreparedStatement stmt = dbCon.prepareCall(query);
oracle.xdb.XMLType myXml = oracle.xdb.XMLType.createXML(conn, xmlString);
stmt.setObject(1, myXml);
stmt.setString(2, "-");
stmt.execute();
// close connection
...
 

Note the statements to create the oracle XmlType and pass it to the database.

Since the character set of this database was WE8ISO8859P15 the inserts failed when the xml string contained special characters, such as japanese characters. The error message was:

java.sql.SQLException: ORA-17155: cannot map Unicode to Oracle character

The best solution for this problem is changing the database character set to AL32UTF8. It would have been nice if Oracle had provided a type NXmlType similar to NVARCHAR2 such that we could use the National Character Set, which was AL16UTF16. But unfortunately this type does not exist (yet). As a matter of fact this is probably the reason why one should always be using character set AL32UTF8 for databases with columns of type XmlType.

So workarounds for this problem should be temporary, until the character set of the database has been changed to AL32UTF8. Such a workaround is temporarily inserting the xml data via a CLOB. In that case the insert succeeds but the special characters in the xml will be replaced by question marks upside down. Here is the code:

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.io.StringReader;
...
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(dsstr);
OracleConnection conn = 
  (oracle.jdbc.OracleConnection)
     (( weblogic.jdbc.extensions.WLConnection)ds.getConnection()).getVendorConnection();
String query = "begin INSERT into myxmls VALUES (sys.xmltype.createXML(?),?); commit; end;";
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareCall(query);
StringReader xmlreader = new StringReader(xmlString);
stmt.setClob(1, xmlreader);
stmt.setString(2, "-");
stmt.execute();
// close connection
...
 

Note the tricks to get an OracleConnection from a DataSource and to convert the xml string to a Clob. The second trick also works with an OracleCallableStatement:

...
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareCall(query);
...