javaoraclejdbcclobojdbc

ORA-22922: nonexistent LOB value in Oracle 18c when fetching CLOB values from scalar subquery

The following statement 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

Is this a known bug? How to work around it?

Solution

  • While I don't know about the bug itself (it must be, there's no reason for this not to work, and apparently, it has been fixed in 23ai), here are some workarounds, knowing this has to do with the scalar subquery:

    Concatenate an empty CLOB

    This query exposes no such problem:

    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

    This also seems to work:

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

    Increase the ojdbc fetch size

    This works, but just delays the problem to a later row:

    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

    This technique is similar to manually circumventing the fetch size impact on the query behaviour, allocate Clob resources, and delay freeing the resource until the end of the query execution:

    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();
    }
}

    Freeing the Clob values earlier seems to bring back the problem.

    Delay the Clob freeing by the fetch size

    Having a buffer of non-freed Clob values exactly of the size of the JDBC fetch size also seems to work. It seems better than the above approaches where tons of data is cached in ojdbc:

    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();
    }
}