Search code examples
javasqlxmloracle-databasedatagrip

Oracle xmlsequence - java.sql.SQLException

I tried to use xmlsequence in this statement with datagrip:

select xmlsequence(extract(river, '/river/cities/*'))
from river_xml
where extractValue(river, '/river/name/text()')='Rhein';

the output was fine:

2020-06-18 19:09:36] 1 row retrieved starting from 1 in 38 ms (execution: 0 ms, fetching: 38 ms)

but from the select statement I got:

<failed to load>
java.sql.SQLException: Interner Fehler: makeJavaArray doesn't support type 2007
    at oracle.sql.ArrayDescriptor.makeJavaArray(ArrayDescriptor.java:1075)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBodyElements(OracleTypeCOLLECTION.java:571)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81ImgBody(OracleTypeCOLLECTION.java:527)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unpickle81(OracleTypeCOLLECTION.java:339)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearizeInternal(OracleTypeCOLLECTION.java:235)
    at oracle.jdbc.oracore.OracleTypeCOLLECTION.unlinearize(OracleTypeCOLLECTION.java:214)
    at oracle.sql.ArrayDescriptor.toJavaArray(ArrayDescriptor.java:790)
    at oracle.sql.ARRAY.getArray(ARRAY.java:301)
    in JdbcHelperImpl.wrapIfNeeded(JdbcHelperImpl.java:461)

I can't find this problem in the internet, so maybe someone here knows how I can solve this? Thanks for your help

Solution

  • The xmlsequence, extract and extractvalue functions are all deprecated. At the moment you're getting a result which is a single collection of type XMLSEQUENCETYPE, with each element of the collection a city node. Presumably it's that collection type that DataGrip isn't happy about.

    You can use xmltable instead, which will give you a result with one row per city:

    select x.*
from river_xml r
cross join xmltable(
  '/river[name="Rhein"]/cities/city'
  passing r.river
  columns city xmltype path '.'
) x;

    You can adapt that to get information about the city in separate columns instead of as an XMLType value instead if you want; it depends what you do with the result.

    db<>fiddle doesn't seem to know what to do with XMLSEQUENCETYPE either, which is fair enough; but you can see the output from the XMLTable query.