5

Oracle's SQL Developer doesn't display the content of XML columns when the datatype XMLType is used. The first lines are displayed ok (if Preferences > Database > Advanced > Display XML Value in Grid is ticked), but once you doubleclick on the little yellow pencil, the "View Value" window remains empty. Curiously, it works if you store the XML in a clob.

CREATE TABLE t (x XMLTYPE, c CLOB);
INSERT INTO t VALUES (XMLTYPE('<x/>'), '<x/>');
COMMIT;
SELECT * FROM t;

empty view value in Oracle 18.1

After a lot of internet search, I found a post by thatJeffSmith saying that it's a known bug and will be fixed soon. And yes, it is working again from version 19.1 onwards. However, at work we are stuck with version 18.2 for a while. So, is there a workaround in 18?

Version    XML View Value
17.3.0.271 ok
17.4.0.355 ok
18.1.0.095 empty
18.2.0.183 empty
19.1.0.094 ok
19.2.1.247 ok
19.4.0.354 ok (but needs modern JDK)

This is how it looks in 19.1:

view values is ok in 19.1

Secondly, I couldn't find a list of bugs for SQL Developer, or a list of fixed bugs, or old release notes. Currently, Oracle's download page lists only the latest three releases 19.1, 19.2 and 19.4, so it's impossilbe to find out when this bug was fixed.

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • 19.2.1 or 19.4...get 19.4 though – thatjeffsmith May 15 '20 at 20:28
  • You're right, have corrected the versions. Can't get 19.4 to work on macOS Catalina (10.15.2), as it doesn't store connections. I have not much experience asking questions of stackoverflow, should I open a separate question for that? – wolφi May 15 '20 at 20:42
  • 2
    Update your JDK, the pwd encryption requires jce jars not found in older JDK 8s .. jdk8u200+ – thatjeffsmith May 15 '20 at 21:46
  • @thatjeffsmith Thanks for the hint! I'll have to wait a little, as Oracle [says](https://www.java.com/en/download/help/catalina_java.xml) "we cannot certify any JDK version on macOS 10.15 yet" – wolφi May 16 '20 at 09:58
  • Certify...I hate that word. We're using it internally on my team with no problems if thats worth anything. You can also add the jce jars yourself https://www.oracle.com/java/technologies/javase-jce8-downloads.html – thatjeffsmith May 16 '20 at 10:31
  • @thatjeffsmith I certainly share your feelings about certify. I just was put off by this warning having spent a day or so to get the instant client running under Catalina. – wolφi May 16 '20 at 17:48

1 Answers1

2

The proper solution is an upgrade to version 19 (or theoretically downgrade to 17).

If you are stuck in Version 18, there is a workaround:

SELECT t.x.getClobVal() FROM t t;

For some strange reason, the table alias is required.

wolφi
  • 8,091
  • 2
  • 35
  • 64