Search My Ramblings

Friday, June 5, 2009

Publishing large XML docs from BPEL to an XMLType column

As is often the case, this was purely a trial-and-error experiment that I got working in a specific way, but there may be other ways as well. In other words, use these instructions at your own risk, as I assume no liability for any problems these may cause in your environment.

Problem Statement
For a customer we had large XML messages that we wanted to save in an XMLType database column to give their power database users the ability to search and manipulate the contents using scripts.

Instructions
There are 2 sets of instructions, one that will be performed by the application server administrator once, and steps that need to be performed for each partner link that wants to use the XMLType column.

ApplicationServer Administrator Instructions

In order to get Toplink to allow larger than 4000 characters in your XML document to be read/written to the XMLType column, you'll need to include the Java archive (.jar) containing the XMLType class to the classpath for Toplink. Fortunately, within the oc4j.jar there's a boot.xml (under META-INF) that creates the Toplink shared library that gets referenced by applications like BPEL, etc. that already references the xdb.jar under {ORACLE_HOME}/toplink/jlib. If you look on your server, you're not likely to see it there (yet). Here's what I did to get the XMLType class included for Toplink automatically then:
  1. Copy the {ORACLE_HOME}/rdbms/jlib/xdb.jar to {ORACLE_HOME}/toplink/jlib
  2. Edit the server.xml under {ORACLE_HOME}/j2ee/oc4j_soa(or the OC4J where BPEL was installed)/config to remove the entry for the rdbms.jlib.xdb.jar for the shared library named "oracle.bpel.common". This avoids the server complaining that there are duplicate references to these classes in multiple classloaders. Notice the oracle.toplink shared library is already imported into the oracle.bpel.common library, which is where it will now be able to reference these classes from now on.
  3. Restart the OC4J instance whose server.xml you just modified.
BPEL Developer Instructions
  1. Create the XMLType column and database table as desired
  2. In your BPEL process, create a partner link (and database connection if not already there!) for the database column. JDeveloper will typically complain that it found an XMLType column that isn't supported. This is normal(but maybe should be fixed by Oracle in a future release :)).
  3. Using a text editor other than JDeveloper, edit the XXXX_toplink_mappings.xml generated for the new partner link, since JDev will not allow you to edit this for some reason.
  4. For the element that maps the XMLType column, change the xsi:type from "direct-mapping" to "direct-xml-type-mapping".
  5. Deploy your process and test. Voila, your process now supports large XML documents with an XMLType column.
Warning: Be careful that if you use the DB Adapter wizard in JDeveloper to regenerate the partner link it will reset your mapping type back to direct-mapping, so steps 3-4 above will need to be repeated.

No comments: