Saving and Loading an XMLType to/from Oracle from Java

First off, you will need ojdbc14.jar, xmlparsenv2.jar, and xdb.jar. You probably already have ojdbc14.jar if you are doing Oracle work, but the other two I had to download.

Lets get our imports going.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

Now, you'll have to create a class, and inside it put this save function. My variable XMLdata is just a string I grabbed from the POST to this Java.

 public void save() {
  Connection conn = null;
  CallableStatement callableStatement = null;
  ResultSet rs = null;
  DataSource dataSource = null;
  InitialContext ctx = null; 
  System.out.println("[save] Starting");
  try {
   ctx = new InitialContext();
   dataSource = (DataSource)ctx.lookup("java:comp/env/yourdatasourcename");
   conn = dataSource.getConnection();
   System.out.println("[save] Fetching");
   callableStatement = conn.prepareCall("{call SAVE(?,?,?,?)}");
   System.out.println("[save] prepare call done, setting values"); 
   callableStatement.setInt(1, var1);
   callableStatement.setString(2, var2);
   callableStatement.setString(3, var3);
   callableStatement.setObject(4, XMLdata); // note that I use setObject instead
   rs = callableStatement.executeQuery();
   System.out.println("[save] Executed the query");
   
   System.out.println("[save] inserted XML data: "+XMLdata);
   rs.close();
   callableStatement.close();
   conn.close();
   System.out.println("[save] Done"); 
  } catch (SQLException e) {
   System.out.println("[save] Saving the form blew up with an SQLException");
   e.printStackTrace();
  } catch (Exception e) {
   System.out.println("[save] Saving the form blew up with an Exception");
   e.printStackTrace();
  }
   
 }

The SAVE stored procedure just takes an integer, two varchar2s, and an XMLtype as input. Nothing exciting here. Let's move on to the trickier part: loading.

 public void load() {
  Connection conn = null;
  CallableStatement callableStatement = null;
  ResultSet rs = null;
  DataSource dataSource = null;
  InitialContext ctx = null; 
  System.out.println("[load] Starting");
  try {
   ctx = new InitialContext();
System.out.println("got context");
   dataSource = (DataSource)ctx.lookup("java:comp/env/yourdatasourcenamehere");
if(dataSource==null) 
System.out.println("Got the datasource but it's null");
else
System.out.println("Got the datasource and it's not null");
   conn = dataSource.getConnection();
System.out.println("Got the connection");
   callableStatement = conn.prepareCall("{call LOAD(?,?,?,?)}");
   System.out.println("[load] prepare call done, setting values"); 
   callableStatement.setInt(1, InstanceId);
   callableStatement.setString(2, ProcessName);
   callableStatement.setString(3, TracingTag);
   callableStatement.registerOutParameter(4, OracleTypes.OPAQUE,"SYS.XMLTYPE"); 
   System.out.println("[load] Registered XMLType out parameter");
   rs = callableStatement.executeQuery();
   System.out.println("[load] Executed the query");
   oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML((oracle.sql.OPAQUE)callableStatement.getObject(4));

   System.out.println("[load] Got XML data: "+xml.getStringVal());
   rs.close();
   callableStatement.close();
   conn.close();
   System.out.println("[load] Done"); 
  } catch (SQLException e) {
   System.out.println("[load] Loading the form blew up with an SQLException");
   e.printStackTrace();
  } catch (Exception e) {
   System.out.println("[load] Loading the form blew up with an Exception");
   e.printStackTrace();
  }
   
 }

The key to loading is to first register an opaque out parameter. There are ways to use other types, but I found that subsequent manipulations of the returned data required starting with this type. You can get an XMLType, but then you need to use the connection in Oracle's libraries, and that breaks other stuff... so on. So use this: callableStatement.registerOutParameter(4, OracleTypes.OPAQUE,"SYS.XMLTYPE");

Opaque can store all sorts of Oracle types. To actually manipulate the XML in a useful way, you have to store it in an XMLType. To do that, create a new XMLType and then, using a cast, assign the opaque variable to it. Like so:

oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML((oracle.sql.OPAQUE)callableStatement.getObject(4));

That's the quick one line version. A little more readable is the following:

  Object xmlobject = callableStatement.getObject(4);
  oracle.sql.OPAQUE xmlopaque= (oracle.sql.OPAQUE) xmlobject;
  oracle.xdb.XMLType xml = oracle.xdb.XMLType.createXML(xmlopaque);

The nice thing about oracle.xdb.XMLType is that it behaves just like an XMLType in Oracle. So you can use the functions you're used to from PL/SQL. getStringVal() returns the string value of the XML for example.