This discussion is archived
6 Replies Latest reply: Jan 9, 2013 9:22 PM by 982700 RSS

java.sql.SQLException: ORA-01461:

982700 Newbie
Currently Being Moderated
Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

I'm getting the above exception while trying to insert a value to the column (varchar 2 (5 BYTES)).

When I inserted a value which is greater than 5 bytes and less than 4000 bytes I'm getting the corresponding exception saying that "value too long...." which is expected.

But to my surprise when I tried to insert a value which is greater than 4000 bytes in the same column, I'm getting the exception (Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column).

Why is this behaviour?

Please suggest.

Oracle version: 10.0.2.0

Thanks in advance

Edited by: 979697 on Jan 3, 2013 4:02 AM
  • 1. Re: java.sql.SQLException: ORA-01461:
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Cause: An attempt was made to insert a value from a LONG datatype into another datatype. This is not allowed.
    Action: Do not try to insert LONG datatypes into other types of columns.
  • 2. Re: java.sql.SQLException: ORA-01461:
    982700 Newbie
    Currently Being Moderated
    Dear,

    Thanks for your reply.
    Small confusion here....

    I'm trying to insert a value which is > 4000 bytes (of type java String) to the column of type varchar2 (2 bytes).
    Ideally the exception expected should be "java.sql.SQLException: ORA-12899: value too large for column ......."

    But I'm getting the exception Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column.

    If I tried to insert a value which is <4000 bytes or >2 bytes to the same field the exception is as expected (java.sql.SQLException: ORA-12899: value too large for column .......).

    Why is this behaviour?

    Please suggest....
  • 3. Re: java.sql.SQLException: ORA-01461:
    Bawer Journeyer
    Currently Being Moderated
    Guthikonda wrote:
    Why is this behaviour?

    Please suggest....
    in Oracle tables, you can define a varchar2 up to 4000byte.
    more than 4000b data should be defined as CLOB/BLOB/LONG or RAW.
  • 4. Re: java.sql.SQLException: ORA-01461:
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    >
    I'm trying to insert a value which is > 4000 bytes (of type java String) to the column of type varchar2 (2 bytes).
    Ideally the exception expected should be "java.sql.SQLException: ORA-12899: value too large for column ......."

    But I'm getting the exception Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column.
    >

    It depends how you are "trying to insert".
    JDBC driver may internally use different setters for different values.
    It looks like when you use setObject and pass a value >4000 it uses setLONG, but the table column type is VARCHAR2 and the query fails with ORA-01461.
    Post your Java code and full error stacks for errors

    Edited by: Mark Malakanov (user11181920) on Jan 8, 2013 10:50 AM
  • 5. Re: java.sql.SQLException: ORA-01461:
    982700 Newbie
    Currently Being Moderated
    Hello,

    JDBC driver : - ojdbc14
    JDBC Driver version : -10.2.0.3.0

    Below is the sample code:

    Object[] params = {"2000€"} //params is an array of 2000 €uros as each €uro symbol consumes 3 bytes.*

    *StringBuilder query = new StringBuilder("INSERT INTO PRODUCT_STRUCTURE_ITEM ( name) values (?));*
    getJdbcTemplate().update(getInsertQuery(), params);

    Error log:

    Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
  • 6. Re: java.sql.SQLException: ORA-01461:
    982700 Newbie
    Currently Being Moderated
    Hello,

    JDBC driver : - ojdbc14
    JDBC Driver version : -10.2.0.3.0

    Below is the sample code:

    Object[] params = {"2000€"} //params is an array of 2000 €uros as each €uro symbol consumes 3 bytes.*

    StringBuilder query = new StringBuilder("INSERT INTO PRODUCT_STRUCTURE_ITEM ( name) values (?));
    getJdbcTemplate().update(getInsertQuery(), params);

    Error log:

    Caused by: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)


    Please suggest...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points