Oracle Database Express Edition (XE)

I'm using the latest Oracle 18c (18.4.0.0.0) version from here: https://hub.docker.com/r/gvenzl/oracle-xe/tags. The following problem no longer reproduces with 23ai. I'm not sure if it has been fixed in a non XE patch version of 18c, if so, please ignore this report. I've also documented this issue on Stack Overflow here: https://stackoverflow.com/q/78900296/521799

The following statement using Java / ojdbc doesn't work on Oracle 18c, but works fine on Oracle 23ai:

try (Statement s = connection.createStatement()) {
    try (ResultSet rs = s.executeQuery(
        """
        SELECT (SELECT to_clob('123412341234') x FROM dual)
        FROM dual
        CONNECT BY LEVEL <= 20
        """
    )) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

After successfully fetching the first 10 rows, the following error is raised:

123412341234
123412341234
123412341234
123412341234
123412341234
123412341234
123412341234
123412341234
123412341234
123412341234
ORA-22922: nonexistent LOB value

It doesn't appear to be strictly ojdbc related, as I can reproduce a similar problem also in SQL*Plus:

SQL> SELECT (SELECT to_clob('123412341234') x FROM dual)
  2  FROM dual
  3  CONNECT BY LEVEL <= 11;

(SELECTTO_CLOB('123412341234')XFROMDUAL)
--------------------------------------------------------------------------------
123412341234
ERROR:
ORA-22922: nonexistent LOB value

Workarounds include:

Concatenate an empty **CLOB**

SELECT (SELECT to_clob('123412341234') x FROM dual) || to_clob('')
FROM dual
CONNECT BY LEVEL <= 20

Wrap the scalar subquery in a **to_clob()** function call

SELECT to_clob((SELECT to_clob('123412341234') x FROM dual))
FROM dual
CONNECT BY LEVEL <= 20

Increase the ojdbc fetch size

try (Statement s = connection.createStatement()) {
    s.setFetchSize(1000);
    try (ResultSet rs = s.executeQuery(
        """
        SELECT (SELECT to_clob('123412341234') x FROM dual)
        FROM dual
        CONNECT BY LEVEL <= 20
        """
    )) {
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }
}

Fetch JDBC **Clob** values, and delay **Clob.free()** calls

try (Statement s = connection.createStatement()) {
    try (ResultSet rs = s.executeQuery(
        """
        SELECT (SELECT to_clob('123412341234') x FROM dual)
        FROM dual
        CONNECT BY LEVEL <= 20
        """
    )) {
        List<Clob> clobs = new ArrayList<>();

        while (rs.next()) {
            Clob clob = rs.getClob(1);
            clobs.add(clob);
            System.out.println(clob.getSubString(1, (int) clob.length()));
        }

        for (Clob clob : clobs)
            clob.free();
    }
}

Delay the **Clob** freeing by the fetch size

try (Statement s = connection.createStatement()) {
    try (ResultSet rs = s.executeQuery(
        """
        SELECT (SELECT to_clob('123412341234') x FROM dual)
        FROM dual
        CONNECT BY LEVEL <= 100
        """
    )) {
        Deque<Clob> clobs = new ArrayDeque<>();

        while (rs.next()) {
            Clob clob = rs.getClob(1);
            clobs.add(clob);
            System.out.println(clob.getSubString(1, (int) clob.length()));

            int size = clobs.size() - s.getFetchSize();
            while (size --> 0)
                clobs.pollFirst().free();
        }

        for (Clob clob : clobs)
            clob.free();
    }
}

The last example really hints at the underlying problem, where it seems that scalar subquery caching and fetch size (row prefetch) caching don't play nicely with each other.