DevOps, CI/CD and Automation

Hi folks. I'm having a problem with inserting data into an XMLType column, and I was wondering if anyone had any ideas how to do this.

I'm loading XML data from a java application. The XML document defines the encoding as "UTF-8"...

<?xml version="1.0" encoding="UTF-8" ?>
<MyObject>
<ItemValue>Here is some text ®</ItemValue>
</MyObject>

Whenever I write this into a Clob and attempt to add it to my table via the SYS.createXML, I get the exception

ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 65533 (\uFFFD)
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

However, if I change the encoding value to "ISO-8859-1", which is the Latin1 character set, I can insert the data without any problems.

Is the XMLType database type Unicode-compatible? At least, can the createXML handle Unicode encoding?

My java code looks as follows:

private void testCharsets(String fileName, String charSetName, boolean convert)
throws SQLException, java.io.FileNotFoundException,
java.io.IOException {
System.out.println("testCharsets(" + fileName + ") - START");

Connection conn = DriverManager.getConnection(ORACLE_URL,
"ehillman", "ehillman");

createTables(conn);

try {
java.io.FileInputStream fis = new java.io.FileInputStream(
new java.io.File(
"C:\\Documents and Settings\\ehillman\\.netbeans\\edh test\\oracle jdbc\\com\\edh\\oracle\\" + fileName));
java.io.BufferedReader in = new java.io.BufferedReader(
new java.io.InputStreamReader(fis, charSetName));

if ((!(charSetName.equals("UTF8"))) && convert) {
//Convert the String to UTF 8
java.io.ByteArrayOutputStream bos = new java.io.ByteArrayOutputStream();
java.io.BufferedWriter convertWriter = new java.io.BufferedWriter(
new java.io.OutputStreamWriter(bos, "UTF8"));

int len = 80;
char buf[] = new char[len];
int numread;

while ((numread = in.read(buf, 0, len)) != -1) {
convertWriter.write(buf, 0, numread);
}
convertWriter.flush();
convertWriter.close();

java.io.ByteArrayInputStream convertedStream =
new java.io.ByteArrayInputStream(bos.toByteArray());
in = new java.io.BufferedReader(
new java.io.InputStreamReader(convertedStream, "UTF8"));
}

conn.setAutoCommit(false);

Statement stmt2 = conn.createStatement();
stmt2.execute("insert into SillyData (XML_STORAGE) values (EMPTY_CLOB())");
System.out.println("empty row inserted");

stmt2 = conn.createStatement();
ResultSet rs = stmt2.executeQuery("select XML_STORAGE from SillyData for update");
System.out.println("Empty CLOB retrieved");

rs.next();
java.sql.Clob clobValue = rs.getClob(1);

oracle.sql.CLOB oraClob = (oracle.sql.CLOB) clobValue;
System.out.println("casted");

java.io.Writer writer = oraClob.getCharacterOutputStream();
char[] buffer = new char[ oraClob.getChunkSize() ];
for (int charsRead = in.read(buffer); charsRead > -1; charsRead = in.read(buffer)) {
writer.write(buffer, 0, charsRead);
}
writer.close();

System.out.println("data written to Clob");

String value = clobValue.getSubString((long) 1, (int) clobValue.length());
System.out.println("clobValue = \n" + value);

PreparedStatement pState = conn.prepareStatement(
"update SillyData set XML_VALUE = sys.XMLType.createXML(?)");
pState.setClob(1, clobValue);
int result = pState.executeUpdate();
System.out.println("executeQuery returned " + result);
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
dropTables(conn);
}

conn.close();

System.out.println("testCharsets - END");
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
try {
TransactionTester tst = new TransactionTester();
tst.testCharsets("UTFData.xml", "UTF8", false);
tst.testCharsets("Latin1Data.xml", "Cp1252", false);
tst.testCharsets("UTFData.xml", "Cp1252", true);
tst.testCharsets("Latin1Data.xml", "Cp1252", true);
} catch (Exception ex) {
ex.printStackTrace();
}
}