Search code examples
javaspringoracle-databasejdbctemplatexmltype

JdbcTemplate insert XmlType failed on larger sizes

I can't insert quite big XML to Oracle XMLType column using JDBC driver and Spring JdbcTemplate.

String isn't small, so I create CLOB and pass it to XMLTYPE(?) in insert command:

String insertSql = "INSERT INTO tab (xmlcol) VALUES (XMLTYPE(?))";
int[] types = new int[] {Types.CLOB};
SqlLobValue xmlLob = new SqlLobValue(xmlString);
Object[] params = new Object[] {xmlLob};
int status = jdbcTemplate.update(insertSql, params, types);

Everything is OK when xmlString is small - for example 2 KB, but when it's bigger (for example 450 KB) SQLException exception occurs with this message:

ORA-01461: can bind a LONG value only for insert into a LONG column

How insert big XML document to XMLType column?

Details: I'm using Database Oracle 12.1, Spring 4.3, ojdbc7 12.1

Solution

  • You can create XMLType in Java code and then add to PreparedStatement parameters,

    There's an example of Uploading XML into XMLTYPE column in database with Spring JDBCTemplate:

    //Next, we have to wrap the byte array in an InputStream to accepted
InputStream is = new ByteArrayInputStream(t.getObject().getBytes());

//Then, instantiate an XMLType object by using native OracleConnection and InputStream of the byte array object
final XMLType xmldoc = new XMLType(conn, is);
...
ps.setObject(3, xmldoc);

    EDIT - solution details:

    Add dependencies to pom.xml which provide :

    <dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>xdb6</artifactId>
    <version>12.1.0.2</version>
</dependency>
 <dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>xmlparserv2</artifactId>
    <version>12.1.0.2</version>
</dependency>

    Extract and unwrap normal Connection object to OracleConnection and use it to create XMLType as described in article linked above. There is also no need to create prepared statement it can be executed by jdbcTemplate:

    String xmlDocument = "<xml>...<very_large>...</xml>"
XMLType xmlType = new XMLType(conn, xmlDocument);
int status = jdbcTemplate.update(insertSql, xmlType);

    Now it must work with XML content above 4 KB (1 MB tested) size or even larger.