Just sharing some information I've learned over the past week about using BaseX
with Oracle using the SQL Module... in particular, to extract XML out of CLOB
fields in Oracle you need to jump through a couple hoops:
To get things set up, the following jars need to be on the classpath (I just
put them into the BaseX lib directory which gets picked up if you start BaseX
using one of the .bat scripts):
- ojdbc6.jar
- xdb6.jar
- xmlparserv2.jar
With this last jar, xmlparserv2.jar, there are a few things to watch for.
First, you need a later version of the jar. Earlier versions are smaller (the
jar for 11g is about 1350KB, the jar you have should probably be at least that
big), and the filename isn't different for different versions. Be sure of what
you have... you can find it in Oracle distributions here:
{ORACLE_HOME}/oracle/produce/{VERSION_NUMBER}/lib/xmlparserv2.jar
The jar can also be found under /lib in the Oracle client distributions.
Next, you will need to reconfigure the out-of-the-box xmlparserv2.jar to use
some standard java components rather than Oracle's:
Change the entries in the files in the META-INF/services as follows:
- for javax.xml.parsers.DocumentBuilderFactory replace
oracle.xml.jaxp.JXDocumentBuilderFactory with
com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl
- for javax.xml.parsers.SAXParserFactory replace
oracle.xml.jaxp.JXSAXParserFactory with
com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl
- for javax.xml.transform.TransformerFactory replace
oracle.xml.jaxp.JXSAXTransformerFactory with
com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl
The answer on this thread has complete instructions on reconfiguring the jar:
http://stackoverflow.com/questions/11853967/errors-with-xmlparserv2-jar-and-oracle-database
Finally, you will need to add the following property setting to the startup
script you are using (basex.bat, basexhttp.bat, basexgui.bat):
-Doracle.jdbc.getObjectReturnsXMLType="false"
This tells Oracle to return the java standard java.sql.SQLXML type that BaseX
is expecting rather than the proprietary oracle.xdb.XMLType.
See: http://docs.oracle.com/cd/E18283_01/java.112/e16548/jdbcvers.htm#BABGHBCC
Here's how I added it to the script:
set PROPS=-Doracle.jdbc.getObjectReturnsXMLType="false"
java -cp "%CP%" "%PROPS%" %VM% org.basex.BaseX %*
Once you have done all of that, you should be able to run an XQuery like this
to get XML out of Oracle:
sql:init("oracle.jdbc.OracleDriver"),
let $conn := sql:connect("jdbc:oracle:thin:@myoracledb.org:1523:mydbsid",
"username","password")
return sql:execute($conn, "SELECT xmltype(MyXMLClob) from MyTable")
If you get escaped XML in you results, you still need to reconfigure your
xmlparserrv2.jar as shown above.
As a side note: the above configuration is also necessary to make the
basexhttp.bat script successfully start the server. If you use the
un-configured xmlparserrv2.jar it will not start.
Hope this helps!
Cheers,
Scott Bednar
-----Original Message-----
From: Bednar, Scott E
Sent: Sunday, March 23, 2014 5:43 PM
To: 'basex-talk@mailman.uni-konstanz.de'
Subject: XML clob w/ SQL module
I'm trying to extract XML stored in a CLOB from an oracle database using the
sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I've tried casting it to XMLType (similar message) and to char (buffer size too
small for the XML I'm dealing with).
Is there a way of doing this short of creating a specialized Java module for
it?
Thanks, Scott
_______________________________________________
BaseX-Talk mailing list
BaseX-Talk@mailman.uni-konstanz.de
https://mailman.uni-konstanz.de/mailman/listinfo/basex-talk
