overcome 32k limit when inserting oracle clob IN p

2019-03-04 01:35发布

问题:

Environment: oracle 11g, spring-jdbc-3.2.2-RELEASE.jar, JDK 1.7, Oracle UCP driver.

I have a stored procedure which insert record to a table with CLOB column. The SP has a CLOB input argument among other IN and OUT arguments. My Java code uses Spring StoredProcedure to call the stored procedure:

public class MyClass extends StoredProcedure {
    public MyClass(){
       .....
       declareParameter(new SqlParameter("content", Types.CLOB));
       .....
    }

    public void insert(){
       HashMap<String,Object> params = new HashMap<String, Object>(37);
       String bigContent = ....; // which contains ASCII chars in my test
       ....
       params.put("content", new SqlLobValue(bigContent));
       ....
       execute(params);
    }
}

The code works fine if bigContent has < 32k chars. If bigContent has, say 50K, chars, it didn't work. I also tested using jdbcTemplate and SqlLobValue to insert into the table directly, everything works fine with bigContent has 50K chars.

I want to use the SP as it does a whole bunch of other stuff and is more efficient than invoking multiple SQL insert, update, and query statements separately.

Anyone know how to get it to work with SP? Or this is the limit I have to handle it differently if bigContent has > 32K chars?

回答1:

PL/SQL has a hard limit of 32k chars if you send the data as a character string. If the parameter is a CLOB you can first create a temp LOB, fill it up with data and then call your PL/SQL procedure with the CLOB object.



回答2:

It depend on your version of Oracle:

  • < 10.1: you can put the SetBigStringTryClob property to true in your DriverManager.
  • >= 10.1: you can use OraclePreparedStatement.setStringForClob doc here.

Also, this article may help you.



回答3:

The limit for CLOB in Oracle is 176TB. 32KB is limit for VARCHAR. Your code somewhere uses implicit conversion from VARCHAR to CLOB. In Oracle CLOB must be OUTPUT paramater - even if you are inserting it. Oracle's API assumes that you create empty_clob() on database side, then you return a LOB locator to aplication. The application then uses this LOB locator as filehandle.

This behaviour is different from other databases.