Oracle12.2数据库中CLOB的JDBC流编码错误

Question

create table oracle_compatibility_test
(
    id           number(19)    not null primary key,
    name         varchar2(500) not null,
    name_as_clob clob          not null
);    

insert all
    into oracle_compatibility_test (id, name, name_as_clob) values (1, 'test1', 'test1')
    into oracle_compatibility_test (id, name, name_as_clob) values (2, 'test2äößrt', 'test2äößrt')
select *
    from dual;

commit;

package de.pie;

import java.io.IOException;
import java.io.Reader;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;

public final class OracleCompatibilityTest {
    //A 12.2 Oracle Database
    private static final String DB1 = "jdbc:oracle:thin:@server1:1521";
    //A 12.1 Oracle Database
    private static final String DB2 = "jdbc:oracle:thin:@server2:1521";

    private static final String USED_DATABASE = DB1;

    public static void main(String[] args) {
        printDatabaseCharset();

        try (Connection connection = DriverManager.getConnection(USED_DATABASE, databaseProperties());
             Statement statement = connection.createStatement();
             ResultSet result = statement.executeQuery(
                     "select name, name_as_clob, convert(name_as_clob, 'AL32UTF8') as utf_8_clob " +
                             "from oracle_compatibility_test " +
                             "order by id asc")) {
            readRow(result);
            readRow(result);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static void printDatabaseCharset() {
        try (Connection connection = DriverManager.getConnection(USED_DATABASE, databaseProperties());
             Statement statement = connection.createStatement();
             ResultSet result = statement.executeQuery(
                     "select * from database_properties where property_name = 'NLS_CHARACTERSET'")) {
            result.next();
            System.out.println("read charset from database: " + result.getString("property_value") + "\n");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static void readRow(ResultSet result) throws SQLException, IOException {
        result.next();

        String name = result.getString("name");
        String nameAsClob = result.getString("name_as_clob");
        Clob clobAsClobObject = result.getClob("name_as_clob");
        String clobObjectAsString = clobAsClobObject.getSubString(1, (int) clobAsClobObject.length());
        String clobReadAsCharacterStream = readFromCharacterStream(result);
        String utf8clob = result.getString("utf_8_clob");


        StringBuilder sb = new StringBuilder();
        sb.append("read name: ")
          .append(name)
          .append("\nname read as clob: ")
          .append(nameAsClob)
          .append("\nname read as clob-object: ")
          .append(clobObjectAsString)
          .append("\nclob read as character-stream: ")
          .append(clobReadAsCharacterStream)
          .append("\nclob converted to utf-8: ")
          .append(utf8clob)
          .append("\n\n\n");

        System.out.println(sb.toString());
    }

    private static String readFromCharacterStream(ResultSet result) throws SQLException, IOException {
        try (Reader reader = result.getCharacterStream("name_as_clob")) {
            StringBuilder stringBuilder = new StringBuilder();
            int c;
            while ((c = reader.read()) != -1) {
                stringBuilder.append((char) c);
            }
            return stringBuilder.toString();
        }
    }

    private static Properties databaseProperties() {
        Properties prop = new Properties();
        prop.put("user", "user");
        prop.put("password", "password");
        return prop;
    }
}

read charset from database: WE8ISO8859P15

read name: test1
name read as clob: test1
name read as clob-object: test1
clob read as character-stream: test1
clob converted to utf-8:  t e s t 1



read name: test2äößrt
name read as clob: test2äößrt
name read as clob-object: test2äößrt
clob read as character-stream: test2äößrt
clob converted to utf-8:  t e s t 2 ä ö ß r t

对于Oracle12.2，输出如下：

read charset from database: WE8ISO8859P15

read name: test1
name read as clob: test1
name read as clob-object: test1
clob read as character-stream: test1
clob converted to utf-8:  t e s t 1



read name: test2äößrt
name read as clob: test2���rt
name read as clob-object: test2���rt
clob read as character-stream: test2���rt
clob converted to utf-8:  t e s t 2 � � � r t

JDBC驱动程序错误地自动检测字符集为UTF-8，但流实际上是在ISO8859-15中。在JDBC8中不能显式设置字符集。从数据库返回的流是在Oracle 12.1下用UTF-8编码的

Answer 1

我们发现了问题并找到了解决方案（这可能更像是一种变通方法）。

在Oracle12.1中，属性Oracle.jdbc.DefaultLobPrefetchSize默认设置为-1。在Oracle12.2下，这个值更改为4000，这意味着数据库将尝试在一个查询中获取所有内容，并将CLOB强制转换为VARCHAR2（如果它的大小在4000以下，请参见此处）。在使用字符集WE8ISO8859P15时，这在Oracle12.2中不起作用。

将此属性设置为-1将再次禁用预取（因此需要另一个数据库访问来获取CLOB列），并且一切正常。