Tried using OracleWebRowset to export and import ResultSet objects.
refer "https://docs.oracle.com/cd/B28359_01/java.111/b31224/jcrowset.htm#BABCFJBE"
OracleWebRowSet rs = new OracleWebRowSet();
System.setProperty("javax.xml.parsers.SAXParserFactory","oracle.xml.jaxp.JXSAXParserFactory");
FileReader fReader = new FileReader("xmlfile.xml");
rs.readXml(fReader);
rs.acceptChanges();
rs.close();
This raises the error:
java.lang.ClassCastException: oracle.jdbc.rowset.OracleWebRowSet cannot be cast to com.sun.rowset.CachedRowSetImpl
at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:275)
at oracle.jdbc.rowset.OracleCachedRowSet.acceptChanges(OracleCachedRowSet.java:1776)
Tried casting the basic WebRowSetImpl() to no avail
the reason I couldn't use the basic WebRowSet is because oracle NVARCHAR2 columns poses issues while writing and reading XML, raised errors like "can't form insert statements" and "not a proper type"
is there any workaround?
I found a solution...
Seems that the rowset writer and reader are not set properly in the OracleCachedRowSet base class of OracleWebRowSet
So to fix the issue, include two lines of code before calling acceptchanges() method
rs.setReader(new OracleCachedRowSetReader());
rs.setWriter(new OracleCachedRowSetWriter());
So the fixed code would look like:
OracleWebRowSet rs = new OracleWebRowSet();
System.setProperty("javax.xml.parsers.SAXParserFactory","oracle.xml.jaxp.JXSAXParserFactory");
FileReader fReader = new FileReader("xmlfile.xml");
rs.readXml(fReader);
rs.setReader(new OracleCachedRowSetReader());
rs.setWriter(new OracleCachedRowSetWriter());
rs.acceptChanges();
rs.close();