When you’d copy & paste the above title into a search engine, you may come across a very similar sounding blog post I wrote two years ago. Today, my dear colleague Dirk asked me on behalf of a customer if it’s possible that this bug is back in 19.7.0. Of course, I said “No”, for sure not. Bug 26380097 was fixed in Oracle 18c already. Gladly, Dirk insisted. So I checked the SR his customer opened.
Photo by Sven Brandsma on Unsplash
And now I can write an unplanned blog post about Special characters show junk in CLOB columns after upgrade to Oracle 19.7.0 with JDBC.
What happened in 2018?
The issue from bug 26380097 was fixed in Oracle 18c. Basically this happened because the server sent CLOB data to the client expecting the client to do a conversion. Unfortunately the client (JDBC) didn’t do this conversion. The server shouldn’t send it unconverted but did exactly this. The fix corrected it and handled the conversion of CLOBs on the server side.
I just checked this “old” issue. It looks like as you will need to apply the fix in Oracle 12.2.0.1 still on top of every RU. MOS Note: 26380097.8 tells you more about patch availability.
What happens in 2020?
Well, at first I did check the SR. And in less than 2 hours after opening, the Support engineer had a suspicion:
Not 100% proven yet for this specific customer case. But I read the bug. And I realized that another one of my lead customers is affected as well. What is leading to this issue? The bug indeed does not “re”-happen again. In this case, an optimization done with Oracle 19.7.0 skips the conversion of LOB data happening on the server before, and expects the client to handle this.
This potentially breaks all JDBC clients out there as soon as the database server gets upgraded to 19.7.0. The fix which is undergoing final tests while I write this will revert to previous behavior as far as I can see. But you need to apply a fix.
For clarification, this most likely happens not with Unicode database character sets but typically with WE8ISO character sets such as WE8ISO8859P1, WE8ISO8859P9, WE8ISO8859P15 and WE8MSWIN1252 (just to name some typical examples).
And finally, this of course is not an upgrade bug. The same thing can happen when you patch from 19.5.0 to 19.7.0. It is an issue which gets introduced with Oracle 19.7.0. The MAIN code has been fixed in 21c. I guess we will see backports being requested quickly. Right now, no one-off patches are available. And I don’t see any workaround mentioned in the bug.
You can check with this link for patches for bug 31244237.
And a MOS Note is going to be published – if the link does not work, the not is not public yet and under review:
- MOS Note: 2686744.1 – Special characters show junk in CLOB columns with JDBC after upgrade to Oracle 19.7.0
- MOS Note: 2690765.1 – How To Solve Bug 31244237- TURKISH CHARACTERS NOT SHOWN CORRECTLY ON CLOB COLUMN | DB 19.7 For JDBC Clients?)
Further Information and Links
- MOS Note: 2686744.1 – Special characters show junk in CLOB columns with JDBC after upgrade to Oracle 19.7.0
- MOS Note: 2319655.1 – Umlauts in WE8ISO8859P15 With Database 12.2.0.1 Showing Junk Characters for CLOB Column
- MOS Note: 2373661.1 – Non English Characters Not Shown Correctly in JDBC Application after Upgrade of Database from 11.2 to 12.2
- Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBC
- Bug 26380097 – georgian characters donot displayed correctly for clob with myclob.getsubstring
- Bug 31244237 – TURKISH CHARACTERS NOT SHOWN CORRECTLY ON CLOB COLUMN | DB 19.7
- Link for patches for bug 31244237
–Mike
Setting “oracle.jdbc.defaultLobPrefetchSize=-1” parameter on client side as workaround as in the case with bug 26380097 solves the problem on database version 19.7 also.
Thanks!
Hi,
we already have >20 19c databases patched with RU 200414 = 19.7.0; and some of them are mostly used with JDBC.
But all our databases are created with database codepage AL32UTF8 = Unicode. And accessed mostly with client codepages WE8MSWIN1252/WE8MSWIN1251/WE8ISO8859P1/WE8ISO8859P15.
So can we somehow tell if we are affected by this bug ?
Johann
Hi Johann,
I think the issue will happen only (at least this is my understanding so far) if the Oracle Database Server’s character set is 8bit. With a Unicode character set, this won’t happen. The client doesn’t matter in my understanding.
Cheers,
Mike
Hallo Mike!
Are German umlauts öäüß affected by this bug? Most our databases use the WE8MSWIN1252 charset.
BR
Hi Alexey,
yes, they are as far as I can see.
Cheers,
Mike
Already wrote yesterday that the problem is solved by setting the JVM parameter on the client side.
Most of our databases was created with CL8MSWIN1251 (cyrillic) codepage.
So the moral of your recent blogs is … don’t go to 19.7!
Nope – but apply the patch 😉
19.7 has turned out to be good, and fixes a lot of things we saw still in 19.6. with large customer installations.
But it needs some tweaks – that’s why I blog about it 🙂
Cheers,
Mike
patches are not available for Windows. Oracle say any fixes will be in next patchset they will NOT release individual patches. We cannot afford to risk hitting one of the errors. Production machines are at 19.3, we only applied 19.7 to test servers before I saw your blog.
This sounds bad, Liz 🙁
Sorry to hear that …
Cheers,
Mike
But at the moment there is no patch?
See the comments from Martin Decker please.
Thanks,
Mike
Hi ,today we had a test case using 19.7 ru applied db with jdbc connection driver used (verified via v$sesison_connect_info) if the drive version is 12.0.3 no problem occured . insert and view chracters in a clob worked fine ; but if the version was 18/19 then character were not seen correctly.
The bug is solved via Patch 31383396: TURKISH CHARACTERS NOT SHOWN CORRECTLY ON CLOB FOR 19.7 WITH WE8ISO8859P9
REF:
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESSION_CONNECT_INFO.html#GUID-9F0DCAEA-A67E-4183-89E7-B1555DC591CE
Thanks Tamer!
Cheers,
Mike
Seems we hit the same bug.
oracle.jdbc.defaultLobPrefetchSize=-1 is an workaround for us.
Unfortunately this tells something about QA as such an easy regression came through.
Please open an SR if you have any questions regarding this.
Thanks,
Mike
Patch is available now!
Thanks Martin!
Patch number is 31383396, by the way.
Thanks Martin!
if we are planning with upgrade to database 19.8 on EBS R12* , is this below bug fix included.
Bug 31244237 : TURKISH CHARACTERS NOT SHOWN CORRECTLY ON CLOB COLUMN | DB 19.7
Actually this page is not: support.oracle.com.
Please raise such questions with Oracle Support in the future.
And no, the patch is not included into any RU yet as far as I can see but it has been requested.
Cheers,
Mike
Hello Mike,
In document “Bugs fixed in each 19.0.0.0.0 Release Update and Release Update Revision” on Metalink (https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=372359613699353&id=2523220.1) it is mentioned that patch 31383396 for bug 31244237 is included in the following RU and RUR for Oracle 19c :
– 19.8.0.0.DBRU:200714
– 19.7.1.0.DBRUR:200714
So I think the best advice to overcome bug 31244237 should be to apply the most recent RU for Oracle 19c which at this moment is RU 19.8.0 (July 2020).
I also can confirm fix 31383396 for bug 31244237 is included after having executed following statement on the inventory from our Oracle 19.8.0 DB software installation :
$ opatch lsinventory -bugs_fixed | grep 31383396
31383396 31281355 Thu Aug 20 15:16:08 CEST 2020 TURKISH CHARACTERS NOT SHOWN CORRECTLY ON CLOB
Greetings,
Chris
Thanks Chris!
Cheers,
Mike
Hi,
we also ran into this bug. It seems that upgrading to OJDBC8-19.3 fixed the problem. Can anyone confirm this? Maybe this is also the reason, why it didn’t occure on oracles regression testing.
Greetings,
Hannes
Hi Hannes,
thanks for this input.
I can’t confirm this – Oracle Support may know.
Cheers,
Mike
We were able to fix this issue by upgrading OJDBC8 to version 19.3.
Thanks Hannes!
Cheers,
Mike