2

I'm trying to insert a java.sql.Clob to an Oracle DB table with a clob column and it fails with the following exception

javax.sql.rowset.serial.SerialClob cannot be cast to oracle.sql.CLOB
    at oracle.jdbc.driver.OraclePreparedStatement.setClob(OraclePreparedStatement.java:6558)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setClob(OraclePreparedStatementWrapper.java:165)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at com.sun.proxy.$Proxy7.setClob(Unknown Source)
    at com.ieml.basedata.sheet.DMLStatement.executeWithKeys(DMLStatement.java:106)
    at com.ieml.basedata.sheet.DMLStatement.execute(DMLStatement.java:26)
    at com.ieml.sheets.lib.IssueArticles$2.doFunction(IssueArticles.java:268)
    at com.ieml.basedata.sheet.SheetServlet.performFunction(SheetServlet.java:271)
    at com.ieml.basedata.sheet.SheetServlet.doPost(SheetServlet.java:106)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:681)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at org.apache.catalina.filters.ExpiresFilter.doFilter(ExpiresFilter.java:1227)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
    at com.ieml.filters.LogFilter.doFilter(LogFilter.java:34)

Oracle version is 21c, ojdbc8 jar java 8.(jdk 8 202).

Here is the code in question

java.sql.Clob dataClob=null;
....
dataClob =  (java.sql.Clob)new javax.sql.rowset.serial.SerialClob((data.get(changedColumns[k])).toString().toCharArray());
st.setClob(j, dataClob); //problematic line;

Previously the code I'm trying to change was using old now-deprecated oracle.sql.CLOB classes, but it was written to support legacy Oracle 10g. I was hoping to get rid of those by using standard java.sql JDBC classes. Upon researching this, I found the following

https://support.oracle.com/knowledge/Middleware/2024807_1.html which seems to be my issue, but unfortunately I don't have access to my Oracle support so couldn't find the solution

Java.lang.ClassCastException: Javax.sql.rowset.serial.SerialClob Cannot Be Cast To Oracle.sql.CLOB Is Reported While Passing A SerialClob Object As Parameter In The setClob Method Of A Statement (Doc ID 2024807.1) Last updated on FEBRUARY 20, 2025

Applies to: JDBC - Version 11.2.0.4.0 and later Information in this document applies to any platform. Symptoms When attempting to insert a Clob into the database using the next code and JDBC 11.2.0.4:

I was able to avoid setClob entirely by using setCharacterStream by using this code

String data_str=(data.get(changedColumns[k])).toString();
try(InputStream in = new ByteArrayInputStream(data_str.getBytes(StandardCharsets.UTF_8));
        InputStreamReader reader = new InputStreamReader(in))
{
    st.setCharacterStream(j, reader,data_str.length());
}

but I was wondering if anyone has idea why setClob fails with the exception in the first place. Any ideas?

2
  • 1
    Tip: StringReader, avoids the whole convert string to byte array, create a ByteArrayInputStream and InputStreamReader (where you forgot to specify the character set!) Commented Oct 11 at 8:56
  • Also, ojdbc8 is not a version of the driver, it merely means it's for Java 8. Commented Oct 11 at 8:56

2 Answers 2

2

Given the exception, it looks like the Oracle implementation expects only its own implementation of java.sql.Clob (i.e. oracle.jdbc.CLOB), and will not accept other implementations.

Also, although oracle.sql.CLOB is deprecated, internally the Oracle driver continues to use it (it's only deprecated for public use, i.e. in user code). It is recommended to use Connection.createClob() to create an instance suitable for your connection instead of instantiating it directly. As documented in the Oracle 21c JDBC driver API doc of oracle.jdbc.CLOB:

Creating a temporary Clob should use Connection.createClob()

java.sql.Clob clob = connection.createClob(); 

Or use one of the alternative methods of creating a clob, like setClob(int, Reader) as suggested by p3consulting in their answer, or setCharacterStream like you already discovered. You should even be able to use setString(j, data_str) directly, but I'm not 100% sure the Oracle JDBC driver actually supports that.

Sign up to request clarification or add additional context in comments.

Comments

0

void setClob(int parameterIndex, Reader reader) so new SerialClob(data_str.toCharArray()).getCharacterStream()) should give you the needed Reader.

2 Comments

new SerialClob(data_str.toCharArray()).getCharacterStream()) is unnecessarily complicated to obtain a Reader from a string. That is what StringReader is for. However, it should be possible to call setString(column, data_str) without going through a clob or reader.
I ended up using String data_str=(data.get(changedColumns[k])).toString(); try(StringReader reader = new StringReader(data_str)) { st.setClob(j, reader); }

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.