5

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) ?

Xorty
  • 18,367
  • 27
  • 104
  • 155

2 Answers2

11

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

  • on SELECT to_clob(XMLElement("name")) from dual I keep getting ORA-1652: unable to extend temp segment by 128 in tablespace temp – Xorty Nov 30 '10 at 21:41
  • Must be something with your installation. It works fine for me (11.1.0.6). Did you try the getClobVal() version? –  Nov 30 '10 at 21:42
  • No, what instance should I use ? – Xorty Nov 30 '10 at 21:57
  • 1
    Simply replace *to_clob(XMLElement("name"))* with *XMLElement("name").getClobVal()* –  Nov 30 '10 at 22:34
  • 1
    yep that worked! thank you so much ... by the way, I've never seen such terrible Java API as this, directly from Oracle ... – Xorty Nov 30 '10 at 22:43
  • In version 11.2.0.2 of the oracle drivers you can use JDBC4.0 calls directly without casting. (Section 2.1.1.21 : http://docs.oracle.com/cd/E11882_01/server.112/e10881/chapter1_2.htm) Took them long enough. – Spencer Kormos Nov 21 '11 at 18:06
  • @SpencerKormos Do you have any snippet for retrieving sys.xmltype as SQLXML. – ramu Nov 17 '15 at 18:49
  • @ramu nope, sorry. Haven't even used Oracle since I commented on this. Not sure when I last used XML either, actually. – Spencer Kormos Nov 29 '15 at 23:44
1

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
Didier Ghys
  • 30,396
  • 9
  • 75
  • 81
no_name
  • 19
  • 1