6 Replies Latest reply: Jan 9, 2013 11:22 PM by 982700 RSS

    java.sql.SQLException: ORA-01461:

    982700
      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
          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
            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
              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)
                >
                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
                  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
                    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...