How To Find Junk Characters In Oracle Table Column

Cheers to my back up colleague Roland Graeff who told me about this consequence at a customer today. And I consider this a pretty serious issue. It can happen that Special characters show junk in CLOB columns after upgrade to Oracle 12.two.0.1. with JDBC.

This is as bad every bit information technology sounds. Roland told me most a case where the application showed weird characters instead of German umlauts (ä, ö, ü, Ä, Ö, Ü) later an upgrade from Oracle Database 12.one.0.2 to 12.two.0.i.

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBC

Roland explained to me that his customer is hitting bug 26380097. The bug title is a chip misleading as it says "GEORGIAN CHARACTERS DONOT DISPLAYED CORRECTLY FOR CLOB WITH MYCLOB.GETSUBSTRING". The outcome does non use to Georgian characters only just can happen with all non-ASCII characters every bit far equally my understanding goes. And so everybody who does shop non-ASCII characters in CLOBs in the database volition need this patch most likely when using a JDBC application. A client from Georgia hit this issue at first. This pb to the bug tagline.

The issue happens considering the server sends CLOB data to the client expecting the client to exercise a conversion but the client (JDBC) does not exercise the conversion, and thus the server shouldn't ship it unconverted. The set up corrects this and handles the conversion of CLOBs on the server side.

You'll get the fix automatically with Oracle Database 18c equally the fix went into 18c. And at that place are a few 1-off patches available on MOS.

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1

Patch 26380097 – Download from MyOracle Support

If the patch is not bachelor on top of your patch bundle for your platform, you may have to request it. Or you lot employ the below mentioned patch bundles which include the set up.

Testcase

On MOS you'll find this notation containing a pretty elementary testcase:

MOS Annotation: 2319655.1
Umlauts in WE8ISO8859P15 With Database 12.2.0.i Showing Junk Characters for CLOB Column

Hence, I don't reproduce it hither. The issue does not exist with Oracle 11.two.0.four, Oracle 12.1.0.ii and Oracle 18c.

You tin find other MOS Notes such as: MOS Note: 2373661.1 – Non English Characters Not Shown Correctly in JDBC Application after Upgrade of Database from xi.2 to 12.ii but they all refer to the same issue.

The Fix

The ready forbug 26380097 is included:

In Oracle 18c
In the October 2017 Bundle Patch for Windows
Is supposed to be included in the July 2018 Update for Oracle 12.ii.0.1

And please never forget:
When y'all apply a unmarried set or a patch parcel, you must run datapatch afterwards. Otherwise the fix may not take effect (and in this example it actually doesn't take effect unless you execute datapatch).

–Mike