5 Replies Latest reply: Sep 15, 2013 11:30 PM by AnshulKaushal RSS

    ORA-12899 - value too large for column

    781708
      I've got this column with VARCHAR2(BYTE 3) that I got mapped with OpenJPA to a String.

      I want update the value from '1' to '-3' but I get a get a ORA-12899 org.apache.openjpa.lib.jdbc.ReportingSQLException with the message ORA-12899 - value too large for column .. (actual:2 maximum: 1). I tried with a jdbc update as well and it didn't help (just got a different Exception with the same message).

      This is with Weblogic 11g configured to use a thin connection to an oracle.jdbc.OracleDriver

      In my (unit)tests I tired to do the update with a plane JDBC connection and it worked flawlessly. Using the ojdbc14.jar (Specification-Version: Oracle JDBC Driver version - "-10.2.0.2.0");

      I figure that it's got do with character encoding. Does anybody got a solution for this problem?
        • 1. Re: ORA-12899 - value too large for column
          757905
          Hi

          This error indicates the problem with Varchar length. can you paste the code of your java method.
          Only thing u should do is to convert the varchar(3 bytes) to varchar(3) and try running again.

          Thanks
          • 2. Re: ORA-12899 - value too large for column
            781708
            Hi,

            I cannot just change the column (as it's a database that has been in production for years and other systems are using the database a well). There exists "-5" values in the column already.

            In my unittest (which works great even though I know I should handle the connection closing better withing try, catch, finally but that isn't the problem):


            ///BEGIN
            Class.forName("oracle.jbdc.OracleDriver");

            String url = "jdbc:oracle:thin:@databasehost:1521:DB";

            OracleConnection connection = DriverManager.getConnection(url, "USERNAME", "p4ssw0rd");

            PreparedStatement statement = connection.prepareStatement("UPDATE MYTABLE SET MYSTATUS = ? WHERE (MYID = ?)");

            statement.setString(1, "-3");
            statement.setInt(2, 63);

            statement.executeUpdate();

            connection.commit();
            connection.close();

            OracleConnection connection = DriverManager.getConnection(url, "USERNAME", "p4ssw0rd");

            statement = connection.prepareStatement("SELECT MYSTATUS FROM MYTABLE WHERE MYID = ?")
            statement.setInt(1, 63);

            ResultSet resultSet = statement.executeQuery();
            resultSet.next();

            String result = resultSet.getString(1);
            resultSet.close();
            connection.close();

            assertEquals("-3", result);


            ////END

            In the my stateless sessionbean

            /// BEGIN

            @Resource("jdbc/myDataSource")
            private DataSource dataSource;
            '
            public void updateStatus(String id, String newValue) throws Throwable{
            OracleConnection connection = (OracleConnection) dataSource.getConnection();

            PreparedStatement statement = connection.prepareStatement("UPDATE MYTABLE SET MYSTATUS = ? WHERE (MYID = ?)");
            //JUST TO TEST but should have used the parameters
            statement.setString(1, "-3");
            statement.setInt(2, 63);

            //Throws exception
            statement.executeUpdate();

            connection.commit();
            }

            //END

            I know that I probably should change the column definition. However with 6+ databases (one for each environment) that is used by several applications I not really keen on changing the column definition.
            • 3. Re: ORA-12899 - value too large for column
              676290
              Can you try with the following change? Don't cast the connection to OracleConnection.

              import java.sql.Connection;
              ...
              Connection connection = dataSource.getConnection();
              • 4. Re: ORA-12899 - value too large for column
                d1cfe7dc-270b-4062-8a14-8bcd722868c4

                Hi

                 

                I got the same issue after migrating Websphere 7.0.0.0 to Weblogic 8.5.5.0, may I know the solution to erase the problem?

                 

                Thanks

                • 5. Re: ORA-12899 - value too large for column
                  AnshulKaushal

                  I believe it comes down to the driver version you are using.

                   

                  1) Can you please let me know what version of driver you are using?

                  2) What is the database version you are using?

                   

                  Thanks