Return cursor from Oracle global temporary tables, error ORA-01410: invalid ROWID

I have a stored procedure which does the following things:

  • insert input records into a global temporary table
  • create a cursor that selects record from another table that inner joins the temporary table
  • return the cursor

e.g.
OPEN p_cursor FOR
SELECT *
FROM emp
INNER JOIN temp_table
ON temp_table.department = emp.department;

Then I tried to execute the stored procedure from java code and I get ORA-01410: invalid ROWID error. If you google it, you’ll see the following explanation.

Cause: A ROWID was entered incorrectly. ROWIDs must be entered as formatted hexadecimal strings using only numbers and the characters A through F. A typical ROWID format is ‘000001F8.0001.0006’.
Action: Check the format, then enter the ROWID using the correct format. ROWID format: block ID, row in block, file ID

This doesn’t help me to solve the problem, but I found a thread on Oracle forum that mentioned about preserve rows (Global Temporary tables for returning resultsets to ODP.NET client app), so in my case, this error occurs because when creating the temporary table, it is set to ON COMMIT DELETE ROWS. To fix this just change it to ON COMMIT PRESERVE ROWS, so your records will still exist while you trying to get the resultset. e.g:

CREATE GLOBAL TEMPORARY TABLE temp_table (
department     VARCHAR2(50))
ON COMMIT PRESERVE ROWS;

Hibernate with Oracle XmlType

The solution is based on Mapping Oracle XmlType to Document from the Hibernate’s website. I don’t need to use C3P0 though.

package mypackage.util;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.Serializable;
import java.io.StringWriter;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;import oracle.jdbc.OracleResultSet;
import oracle.sql.OPAQUE;
import oracle.xdb.XMLType;

import org.hibernate.HibernateException;
import org.hibernate.usertype.UserType;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;
public class HibernateXMLType implements UserType, Serializable {

private static final long serialVersionUID = 2308230823023l;
private static final Class returnedClass = Document.class;
private static final int[] SQL_TYPES = new int[] { oracle.xdb.XMLType._SQL_TYPECODE };

public int[] sqlTypes() {
return SQL_TYPES;
}

public Class returnedClass() {
return returnedClass;
}

public int hashCode(Object _obj) {
return _obj.hashCode();
}

public Object assemble(Serializable _cached, Object _owner)
throws HibernateException
{
try {
return HibernateXMLType.stringToDom((String)_cached);
}
catch (Exception e) {
throw new HibernateException(“Could not assemble String to Document”, e);
}
}

public Serializable disassemble(Object _obj)
throws HibernateException
{
try {
return HibernateXMLType.domToString((Document)_obj);
}
catch (Exception e) {
throw new HibernateException(“Could not disassemble Document to Serializable”, e);
}
}

public Object replace(Object _orig, Object _tar, Object _owner) {
return deepCopy(_orig);
}

public boolean equals(Object arg0, Object arg1)
throws HibernateException
{
if(arg0 == null && arg1 == null) return true;
else if (arg0 == null && arg1 != null ) return false;
else return arg0.equals(arg1);
}

public Object nullSafeGet( ResultSet rs, String [ ] names, Object arg2 )
throws HibernateException, SQLException
{
XMLType xmlType = null;
Document doc = null;
try {
OPAQUE op = null;
OracleResultSet ors = null;
if (rs instanceof OracleResultSet) {
ors = (OracleResultSet)rs;
} else {
throw new UnsupportedOperationException(“ResultSet needs to be of type OracleResultSet”);
}
op = ors.getOPAQUE(names[0]);
if(op != null) {
xmlType = XMLType.createXML ( op );
}
doc = xmlType.getDOM();
}finally {
if (null != xmlType) {
xmlType.close();
}
}
return doc;
}

public void nullSafeSet( PreparedStatement st, Object value, int index )
throws HibernateException, SQLException
{
try {
XMLType xmlType = null;
if (value != null) {
xmlType = XMLType.createXML(st.getConnection(),HibernateXMLType.domToString((Document)value));
}
st.setObject(index, xmlType);
}
catch (Exception e) {
throw new SQLException(“Could not convert Document to String for storage”);
}
}

public Object deepCopy(Object value)
throws HibernateException
{
if (value == null) return null;

return (Document)((Document)value).cloneNode(true);
}

public boolean isMutable() {
return false;
}

public static String domToString(Document _document)
throws TransformerException
{
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer = tFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, “yes”);
DOMSource source = new DOMSource(_document);
StringWriter sw=new StringWriter();
StreamResult result = new StreamResult(sw);
transformer.transform(source, result);
return sw.toString();
}

public static Document stringToDom(String xmlSource)
throws SAXException, ParserConfigurationException, IOException
{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
return builder.parse(new ByteArrayInputStream(xmlSource.getBytes(“UTF-8”)));
}
}

In the hbm.xml file set the property to be:

<property
name=”xmlMessage”
type=”mypackage.util.HibernateXMLType”
column=”MESSAGE_XML”
/>

In the .java file set the field to be:

private Document  xmlMessage;public Document getXmlMessage() {
return xmlMessage;
}public void setXmlMessage(Document xmlMessage) {
this.xmlMessage= xmlMessage;
}