1

When I execute the following SQL using TOAD against an Oracle 11g database, the fully formed XML is returned successfully:

With T As (SELECT dbms_xmlgen.getxml('SELECT m.trans_message FROM xml_nodes_ams_in a, message m WHERE a.id = m.msg_id AND a.UPN IN(''A30971016528VE8K'',''A30971016529VE84'') ORDER BY a.upn ASC'
  ) As output_xml from dual
) select dbms_xmlgen.Convert(output_xml,1) from T

However, when I execute the exact same SQL against our newly installed Oracle 12c database, some of the XML data appears to be missing (around 5000 characters).

I have discussed this with the DBA who reckons its a client issue rather than a database issue as he says there is no setting against the database that would cause this.

Has anyone got any advise on how I can progress this issue?

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Luke_Skywalker007
  • 117
  • 1
  • 13
  • probably there is incomplete data in the 12c Database. check if both databases contain the same count of data in the table `xml_nodes_ams_in` – Migs Isip Apr 12 '17 at 13:25
  • Both databases contain the same data. When I manually extract the data using TOADs filter capabilities the XML is well formed and complete. – Luke_Skywalker007 Apr 12 '17 at 13:33
  • Is the final CLOB being truncated, or is there data missing from within the XML? How much data is shown? Is there a setting in Toad similar to the SQL\*Plus `set long` that determines how much of the CLOB is shown - maybe at connection level if you're using the same client to access both DBs? – Alex Poole Apr 12 '17 at 14:26
  • Are you running with the 12C client software? – APC Apr 12 '17 at 14:39
  • @AlexPoole - The XML is missing from within the XML (not being truncated). Also, most of the data is shown, its only a small snippet of the XML that is missing. Ive tried to look at the settings within TOAD however its quite an old version and doesnt support the 12c client software therefore that might be contributing to the issue. I currently connect to both the 11g and 12c database using a 10g client. Im in the process of downloading the new SQL developer using a 12c client and ill report back my findings. – Luke_Skywalker007 Apr 12 '17 at 15:25
  • @APC - As per my comments to Alex above, Im currently using a 10g client to connect to both databases and will check using a 12c client and report back to you. – Luke_Skywalker007 Apr 12 '17 at 15:26
  • I suspect that is the answer. Oracle do a decent job of backwards compatibility but two *major* versions​is a stretch. – APC Apr 12 '17 at 15:48
  • @APC - OK Ive managed to download the most recent version of SQL Developer and pointed it to an instant client (version 12.2c) on my machine - however when I run the above SQL a small amount of the XML is still missing. I just find it strange that this works against the 11g database and not the 12c database ... could it be a bug in 12c database? Could it be a bug with the XML functions Im using within my SQL? – Luke_Skywalker007 Apr 19 '17 at 14:02
  • Well it *could* be either of those things or something else altogether, There's not much we can do to debug your environment remotely. – APC Apr 19 '17 at 14:13
  • @APC - Thats true. Thanks for your help thus far and if I figure it out ill post the solution here. – Luke_Skywalker007 Apr 19 '17 at 14:27
  • @APC - there appears to be a bug with the dbms_xmlgen.Convert function. When I only run the dbms_xmlgen.getxml part of my SQL code, ALL the data is extracted successfully. I then manually converted the escaped characters and checked that everything was OK with the XML. However, when I then run this extracted XML through the convert function, some of the data is missing. This wasnt present in Oracle 11g therefore looks like a bug with the convert function. Also, when I cut down the size of the XML and run the convert function it works so it must be related to the size of the XML being used. – Luke_Skywalker007 Apr 20 '17 at 08:50
  • @APC - I raised a service request with Oracle and they came back to me and advised that there is a bug with the dbms_xmlgen.Convert function within Oracle 12.1 that was fixed in Oracle 12.2. Basically the function fails with XML greater than 120 KB. – Luke_Skywalker007 Apr 25 '17 at 11:56

1 Answers1

1

I raised a service request with Oracle and they came back to me and advised that there is a bug with the dbms_xmlgen.Convert function within Oracle 12.1 that was fixed in Oracle 12.2. Basically the function fails with XML greater than 120 KB.

Luke_Skywalker007
  • 117
  • 1
  • 13