Java: Oracle XMLType + JDBC

Question

How can I get oracle XMLElement to JDBC?

java.sql.Statement st = connection.createStatement(); // works
oracle.jdbc.OracleResultSet rs = st.execute("SELECT XMLElement("name") FROM dual");
rs.getString(1); // returns null, why?
oracle.sql.OPAQUE = (OPAQUE) rs.getObject(1); // this works, but wtf is OPAQUE ?

Basically, I want to read String like <name> </name> or whatever XML formatted output. But I always fail to cast output to anything reasonable. Only weird oracle.sql.OPAQUE works, but I totally dont know what to do with that. Even toString() is not overriden!

Any ideas? How to read Oracle's (I am using Oracle 10.0.2) XMLElement (XMLType) ?

Answer 1

You can't. Oracle's JDBC driver does not support the JDBC XML type properly.

The only thing you can do, is to convert the XML as part of the query:

SELECT to_clob(XMLElement("name")) from dual

Then you can retrieve the XML using getString()

alternatively you can also use XMLElement("name").getClobVal(), but again this is part of your query and it can be accessed as a String from within your Java class

Answer 2

ORA-1652: unable to extend temp segment by 128 in tablespace temp is a totally different error, nothing to be with XMLElement.

It is just that you hava to set your temp file to auto resize or give it a bigger size:

 ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/oracle/temp01.dbf' SIZE 10M AUTOEXTEND ON

ALTER DATABASE TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/oracle/temp01.dbf' RESIZE 200M