CODEWITHSUNDEEP
javaspringoracle-database
nufanme
nufanme

Reputation: 25

ORA-01401: inserted value too large for column when doing SELECT query containing LIKE

I'm currently getting the below stack trace when trying to execute an SQL query using Java, Spring and Oracle's oci driver to connect a Rdb database. The column ORGANIZATION_NAME is defined in the database as CHAR(26), the value of ORGANIZATION_NAME in the database is Commercial Flooring Soluti and the value I'm using for the LIKE clause is %Commercial Flooring Solutitions%. I've confirmed that the maximum value for the first parameter is 26 which is coming from the database. My question is does anybody know why this is happening for a SELECT statement? I would've expected the database/driver to truncate and handle this. Does it have something to do with the column type being CHAR(26)? It's unusual that the date columns are being set to 2000 (see trace log).

13:03:25,073 SEVERE [au.com.blah.http.logging.HttpLoggingFilter] (http- org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT
    header_record_uid,
    organization_name,
    organization_number
FROM
    direct_update_header@d
WHERE
    UPPER(organization_name) LIKE UPPER(?)
AND
    posting_date BETWEEN TO_TIMESTAMP(?) AND TO_TIMESTAMP(?)
ORDER BY
    posting_date DESC
]; ORA-01401: inserted value too large for column; nested exception is java.sql.SQLDataException: ORA-01401: inserted value too large for column
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:82) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192) [spring-jdbc-4.1.4.RELEASE.jar:4.1.4.RELEASE]

Trace log:

FROM
    direct_update_header@d
WHERE
    UPPER(organization_name) LIKE UPPER(:1 )
AND
    posting_date BETWEEN TO_TIMESTAMP(:2 ) AND TO_TIMESTAMP(:3 )
ORDER BY
    posting_date DESC
statement id: 036E70F0
2015-08-13 13:50:50.459::gta.gtachecksqlcode: Rdb returned SQLCODE: 0 for PREPARE
2015-08-13 13:50:50.459::gta.sequence_check: entry
2015-08-13 13:50:50.459::gta.sequence_check: exit status=0
2015-08-13 13:50:50.459::gta.gtapars: exit status=0
2015-08-13 13:50:50.459::*** GTAISDDL ***
2015-08-13 13:50:50.460::gtoosq.gtoosq: exit status=0
2015-08-13 13:50:50.460::gtoall.gtoopr: exit status = 0
2015-08-13 13:50:50.460::gtoall.gtopbnd: entry
2015-08-13 13:50:50.460::gtobr.gtobrp: entry
gtobr.gtobrp: BRPDEF:.....cursor : 1
gtobr.gtobrp:        ........pos : 1
gtobr.gtobrp:        .....oacdef : 7acb1818
gtobr.gtobrp:        .# oacdef's : 3
gtobr.gtobrp:        ........uac : 0
gtobr.gtobrp:        ...uac lnth : 0
2015-08-13 13:50:50.460::*** GTGBNDONE ***
2015-08-13 13:50:50.461::gta.gtadbnd: entry
statement id: 036E70F0
2015-08-13 13:50:50.461::gta.gtachecksqlcode: Rdb returned SQLCODE: 0 for DESCRIBE INPUT
gta.gtadbnd: SQLDA2 for (1): Describe Input
Cursor number 1, number of items 3
SQLVAR Item 0, Bindnam 1, Bindcol 1
type 449, octet_len 30, sqllen 26
data NULL
SQLVAR Item 1, Bindnam 2, Bindcol INSTR
type 449, octet_len 2004, sqllen 2000
data NULL
SQLVAR Item 2, Bindnam 3, Bindcol INSTR
type 449, octet_len 2004, sqllen 2000
data NULL

Upvotes: 0

Views: 3391

Answers (3)

Andrew Spencer
Andrew Spencer

Reputation: 16474

I have seen this error when running a SELECT on a view that contained a

cast(foo as VARCHAR2(20))

where the underlying type of foo was actually NVARCHAR2(255). It was fixed by changing to

cast(foo as VARCHAR2(255 char))

However the error does not occur in a minimal example - the value is silently truncated instead. I haven't time now to narrow down further, but it may help other visitors to this question.

Upvotes: 0

nufanme
nufanme

Reputation: 25

UPDATE:

I've come up with a workaround which is displayed in the below test. Rather than include the '%' for the LIKE clause as part of the parameter binding in the MapSqlParameterSource, I've moved it to the SQL itself.

public class JdbcNamedTemplate_Select_WithBindVariableWithoutLikeWildcard_ExecutesSuccessfully_IT extends AbstractIntegrationTest {

private String query;
private NamedParameterJdbcTemplate jdbcTemplate;
private MapSqlParameterSource params;
private List<Map<String, Object>> result;
private Exception exception;

@Override
public void given() {
    query = "SELECT * FROM direct_update_header@d WHERE UPPER(organization_name) LIKE '%' || UPPER(:organisationName) || '%' " +
            "AND posting_date BETWEEN TO_TIMESTAMP(:fromDate) AND TO_TIMESTAMP(:toDate)";
    jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

    params = new MapSqlParameterSource();
    params.addValue("organisationName", String.format("%26s", "X"));
    params.addValue("fromDate", new Timestamp(DateUtils.now().toDate().getTime()));
    params.addValue("toDate", new Timestamp(DateUtils.now().toDate().getTime()));

}

@Override
public void when() {
    try {
        result = jdbcTemplate.queryForList(query, params);
    } catch(Exception e) {
        exception = e;
    }
}

@Override
public void then() {
    assertNull(exception);
}
}

Upvotes: 0

Maulik Shah
Maulik Shah

Reputation: 402

The issue seem to be possible for columns of type NCHAR or NVARCHAR2 but not for CHAR or VARCHAR2

Refer Why am I getting ORA-01401: inserted value too large for column - when I'm not inserting? where user faces a similar issue with NVARCHAR2

Looking at your query, you seem to be querying data via a DB Link (direct_update_header@d)

You can try using CHAR semantics in your database. It is strongly recommended when sharing data over database links. Refer this page

Upvotes: 1

Related Questions