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

Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBCThanks to my support colleague Roland Graeff who told me about this issue 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.2.0.1. with JDBC.

This is as bad as it sounds. Roland told me about a case where the application showed weird characters instead of German umlauts (ä, ö, ü, Ä, Ö, Ü) after an upgrade from Oracle Database 12.1.0.2 to 12.2.0.1.

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 bit misleading as it says “GEORGIAN CHARACTERS DONOT DISPLAYED CORRECTLY FOR CLOB WITH MYCLOB.GETSUBSTRING“. The issue does not apply to Georgian characters only but can happen with all non-ASCII characters as far as my understanding goes. So everybody who does store non-ASCII characters in CLOBs in the database will need this patch most likely when using a JDBC application. A customer from Georgia hit this issue at first. This lead to the bug tagline.

The issue happens because the server sends CLOB data to the client expecting the client to do a conversion but the client (JDBC) does not do the conversion, and thus the server shouldn’t send it unconverted. The fix corrects this and handles the conversion of CLOBs on the server side.

You’ll get the fix automatically with Oracle Database 18c as the fix went into 18c. And there are a few one-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 available on top of your patch bundle for your platform, you may have to request it. Or you use the below mentioned patch bundles which include the fix.

Testcase

On MOS you’ll find this note containing a pretty simple testcase:

Hence, I don’t reproduce it here. The issue does not exist with Oracle 11.2.0.4, Oracle 12.1.0.2 and Oracle 18c.

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

The Fix

The fix for bug 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.2.0.1

And please never forget:
When you apply a single fix or a patch bundle, you must run datapatch afterwards. Otherwise the fix may not take effect (and in this case it really doesn’t take effect unless you execute datapatch).

–Mike

 

 

8 thoughts on “Special characters show junk in CLOB columns after upgrade to Oracle 12.2.0.1 with JDBC

  1. Hi Mike,

    thank you for that important update!

    The testcase shows that this bug could hit me everytime I insert umlauts into a clob column. The values are not destroyed only once after the upgrade, like your headline suggests.

    Do you know if the problem persists when using UTF charactersets?

    Cheers
    Kay.

    • Kay,

      somebody on LinkedIn did ask the exact same question.

      I’m not a client person. I suspect that the issue may not happen with UTF charactersets for two reasons:
      /a/ the JDBC client should operate with Unicode per se and therefore may not require a conversion when data gets sent from the server
      /b/ all information in bugs and SRs I have seen is bound to WE8 or EE8 charactersets but not to Unicode.

      Unfortunately I can’t tell it right now. I have sent an email to the bug owner and asked for clarification. As soon as I have more information I will update the blog post.

      Cheers,
      Mike

  2. Hi Mike,
    tried to reproduce the testcase using SQL*Developer 4.1.4.21 but all Umlaut characters get coded and displayed correctly as well in SQL*Plus as in SQL*Developer. What’s wrong with my understanding here?
    Best regards, Olaf

    Database is on the following patchlevel:
    [oracle@xxxxxx~]$ opatch lspatches
    24923080;
    25904273;
    26729611;
    26635944;OJVM RELEASE UPDATE: 12.2.0.1.171017 (26635944)
    26389300;
    27020386;OCW Interim patch for 27020386
    26366517;
    26710464;Database Release Update : 12.2.0.1.171017 (26710464)

    OPatch succeeded.
    [oracle@xxxxx~]$ opatch lsinv -local|grep 26380097

    (should be affected)

    set lin 220
    set pages 30
    column v_varchar format a20
    column v_clob format a20
    column concat_clob_varchar format a30
    column upper_clob format a20
    column initcap_clob format a20
    column tochhar_clob format a20
    column dump_clob format a100
    column dump_varchar format a100
    column CLIENT_VERSION format a12
    column CLIENT_CHARSET format a12
    column network_service_banner format a70
    column parameter format a30

    select sys_context(‘userenv’,’sid’) from dual;

    SYS_CONTEXT(‘USERENV’,’SID’)
    ——————————————————————-
    214

    select sid,network_service_banner,client_version,client_driver,client_charset,client_lobattr from V$SESSION_CONNECT_INFO where sid=214;

    SID NETWORK_SERVICE_BANNER CLIENT_VERSI CLIENT_DRIVER CLIENT_CHARS CLIENT_LOBATTR
    ———- ———————————————————————- ———— —————————— ———— ———————–
    214 TCP/IP NT Protocol Adapter for Linux: Version 12.2.0.1.0 – Production 12.2.0.1.0 jdbcthin : 12.2.0.1.0 Unknown Client Temp Lob Rfc On
    214 Encryption service for Linux: Version 12.2.0.1.0 – Production 12.2.0.1.0 jdbcthin : 12.2.0.1.0 Unknown Client Temp Lob Rfc On
    214 Crypto-checksumming service for Linux: Version 12.2.0.1.0 – Production 12.2.0.1.0 jdbcthin : 12.2.0.1.0 Unknown Client Temp Lob Rfc On

    select * from nls_database_parameters;

    PARAMETER VALUE
    —————————— —————————————————————-
    NLS_RDBMS_VERSION 12.2.0.1.0
    NLS_NCHAR_CONV_EXCP FALSE
    NLS_LENGTH_SEMANTICS BYTE
    NLS_COMP BINARY
    NLS_DUAL_CURRENCY ?
    NLS_TIMESTAMP_TZ_FORMAT DD.MM.RR HH24:MI:SSXFF TZR
    NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_FORMAT DD.MM.RR HH24:MI:SSXFF
    NLS_TIME_FORMAT HH24:MI:SSXFF
    NLS_SORT GERMAN
    NLS_DATE_LANGUAGE GERMAN
    NLS_DATE_FORMAT DD.MM.RR
    NLS_CALENDAR GREGORIAN
    NLS_NUMERIC_CHARACTERS ,.
    NLS_NCHAR_CHARACTERSET AL16UTF16
    NLS_CHARACTERSET WE8MSWIN1252
    NLS_ISO_CURRENCY GERMANY
    NLS_CURRENCY ?
    NLS_TERRITORY GERMANY
    NLS_LANGUAGE GERMAN

    20 rows selected

    select * from nls_session_parameters;
    PARAMETER VALUE
    —————————— —————————————————————-
    NLS_LANGUAGE AMERICAN
    NLS_TERRITORY AMERICA
    NLS_CURRENCY $
    NLS_ISO_CURRENCY AMERICA
    NLS_NUMERIC_CHARACTERS .,
    NLS_CALENDAR GREGORIAN
    NLS_DATE_FORMAT DD-MON-RR
    NLS_DATE_LANGUAGE AMERICAN
    NLS_SORT BINARY
    NLS_TIME_FORMAT HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY $
    NLS_COMP BINARY
    NLS_LENGTH_SEMANTICS BYTE
    NLS_NCHAR_CONV_EXCP FALSE

    17 rows selected

    drop table clob_test;
    Table CLOB_TEST dropped.

    create table clob_test(v_varchar varchar(100), v_clob clob) tablespace tools;
    Table CLOB_TEST created.

    insert into CLOB_TEST values (‘varchar: äääää’, ‘clob: üüüüüü’);
    1 row inserted.

    insert into CLOB_TEST values (‘varchar: äöüß’, ‘clob: äöüß’);
    1 row inserted.

    insert into clob_test values(to_char(‘ääää’), to_char(‘ääää’));
    1 row inserted.

    commit;
    Commit complete.

    ################################################################################################
    select * from CLOB_TEST;

    V_VARCHAR V_CLOB
    ——————– ——————–
    varchar: äääää clob: üüüüüü
    varchar: äöüß clob: äöüß
    ääää ääää

    ################################################################################################
    select
    dump(to_char(v_clob),1010) dump_clob,
    dump(to_char(v_varchar),1010) dump_varchar,
    V_VARCHAR ,
    V_CLOB,
    concat(v_clob,v_varchar) concat_clob_varchar,
    upper(v_clob) upper_clob,
    initcap(v_clob) initcap_clob,
    to_char(v_clob) tochhar_clob
    from CLOB_TEST;

    DUMP_CLOB DUMP_VARCHAR
    —————————————————————————————————- —————————————————————————————————-
    V_VARCHAR V_CLOB CONCAT_CLOB_VARCHAR UPPER_CLOB INITCAP_CLOB TOCHHAR_CLOB
    ——————– ——————– —————————— ——————– ——————– ——————–
    Typ=1 Len=12 CharacterSet=WE8MSWIN1252: 99,108,111,98,58,32,252,252,252,252,252,252 Typ=1 Len=14 CharacterSet=WE8MSWIN1252: 118,97,114,99,104,97,114,58,32,228,228,228,228,228
    varchar: äääää clob: üüüüüü clob: üüüüüüvarchar: äääää CLOB: ÜÜÜÜÜÜ Clob: Üüüüüü clob: üüüüüü

    Typ=1 Len=10 CharacterSet=WE8MSWIN1252: 99,108,111,98,58,32,228,246,252,223 Typ=1 Len=13 CharacterSet=WE8MSWIN1252: 118,97,114,99,104,97,114,58,32,228,246,252,223
    varchar: äöüß clob: äöüß clob: äöüßvarchar: äöüß CLOB: ÄÖÜß Clob: Äöüß clob: äöüß

    Typ=1 Len=4 CharacterSet=WE8MSWIN1252: 228,228,228,228 Typ=1 Len=4 CharacterSet=WE8MSWIN1252: 228,228,228,228
    ääää ääää ääääääää ÄÄÄÄ Ääää ääää

    • Olaf,

      the client has to be a JDBC client. I’m not sure about the SQL Developer but SQL Plus for sure is not using JDBC.
      “So everybody who does store non-ASCII characters in CLOBs in the database will need this patch most likely when using a JDBC application”

      With SQL Plus (and I’d assume the same applies to SQL Developer) you won’t see the issue. It happens when a JDBC connection is established and the server sends unconverted data because it believes the client will do the conversion – but it doesn’t.

      Cheers,
      Mike

      • Hi Mike,
        a) thanks for your reply!
        b) I understood, that JDBC:thin is a factor and that SQL Developer can use different client options to connect to a database. JDBC:thin is the default for SQL Developer and I verified this by checking the CLIENT_DRIVER column of the V$SESSION_CONNECT_INFO rows concerning my session (showing “jdbcthin : 12.2.0.1.0”) (see above).
        And now, I’m perplexed about not beeing able to reproduce the issue although providing all the conditions described in Doc. 2319655.1. (I tested different JDBC driver versions and WE8ISO8859P1 + WE8MSWIN1252 databases – all below the patchlevel including the fix- but Umlauts are displayed correctly in SQL Developer so far…)
        Best regards, Olaf

        • Hi Olaf,

          sorry but this goes beyond what I can deliver here. If it works for you without issue, I’m happy. But as you can see in the comments section, others have seen this as well. I won’t claim that it happens to everybody nor do I have the insights into the code part. I just wanted to warn people that this can be an issue, especially as we here use these Umlauts and Sharp-S quite often.

          Thanks and sorry that I can’t help you!
          Mike

  3. Hi all,
    we were “able” to reproduce the issue after an upgrade to 12.2.0.1 and using JDBC 12.2 (characterset is WE8ISO8859P1). Thanks to the invention of restore points we were able to rewind the whole show. 😉

    The good news are that the patch is included in July RU. After patching the 12.2. home and upgrading again, everything is fine now.

    Regards
    Kay.

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.