Thanks 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.
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:
- MOS Note: 2319655.1
Umlauts in WE8ISO8859P15 With Database 12.2.0.1 Showing Junk Characters for CLOB Column
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
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
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
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.
Thanks Kay!!!
This is very helpful!
Mike