CODEWITHSUNDEEP
javastringoracle-databasespring
user710818
user710818

Reputation: 24248

How to workaround setString max 32766 chars in Oracle?

I use java and spring to write data in Oracle database. Code like:

org.springframework.jdbc.core.support.JdbcDaoSupport.getJdbcTemplate.
update(String sql, Object[] args) ;

All args are String. Sometimes I receive error: 

 java.sql.SQLException: setString can only process strings of 
less than 32766 chararacters

What can I do in this situation? Thanks.

Upvotes: 0

Views: 2299

Answers (1)

William Feirie
William Feirie

Reputation: 644

I have an example,the sql is:

CREATE TABLE t_customer (
id         LONG         NOT NULL,
first_name VARCHAR2(32) NOT NULL,
last_name  VARCHAR2(32) NOT NULL,
last_login TIMESTAMP    NOT NULL,
comments   CLOB         NOT NULL
)

the configuration is below:

<bean id="nativeJdbcExtractor"
       class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>
<bean id="lobHandler"
      class="org.springframework.jdbc.support.lob.OracleLobHandler">
    <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
</bean>

the code:

private void runInTemplate() {
    this.jdbcTemplate.update(
            "insert into t_customer " +
                    "(id, first_name, last_name, last_login, comments) " +
                    "values (?, ?, ?, ?, ?)",
            new PreparedStatementSetter() {
                public void setValues(PreparedStatement ps)
                        throws SQLException {
                    ps.setLong(1, 2L);
                    ps.setString(2, "Jan");
                    ps.setString(3, "Machacek");
                    ps.setTimestamp(4,
                            new Timestamp(System.currentTimeMillis()));
                    lobHandler.getLobCreator().setClobAsString(ps, 5,
                            "This is a loooong String!");
                }
            });
}

Upvotes: 1

Related Questions