12 Replies Latest reply: Dec 17, 2009 5:13 AM by 741742 RSS

    ORA-01461 error using jdbc 10.2.0.1.0

    222267
      I am using the oracle jdbc 10.2.0.1.0 ( the latest version) and oracle 10g (10.1.0.4.0) database. Sometimes, the error "ORA-01461: can bind a LONG value only for insert into a LONG column' if I use a java program to insert record to a table containing a 'long raw' field. Previously, the same java program work well with oracle 9i.

      Interestingly, the 'ORA-01461' only SOMETIMES happen. If the problem appears, I need to rename the table and create a new one and then the data can be inserted.
        • 1. Re: ORA-01461 error using jdbc 10.2.0.1.0
          Avi Abrami
          hcxclty,
          I get the impression you are creating many [temporary] tables and then populating them. This makes me think you are probably trying to use Oracle in a way that it was not meant for.

          Even though a Subaru Justy and a Formula One racer are both motor vehicles, you wouldn't race a Subaru Justy in a Grand Prix and you wouldn't drive a Formual One racer in city streets among traffic.

          Perhaps you could post some code and the entire error message and stack trace you are getting, and I may be able to help you some more.

          Good Luck,
          Avi.
          • 2. Re: ORA-01461 error using jdbc 10.2.0.1.0
            222267
            (1) The error appears (sometimes but not always) when new records are inserted
            into a table with the following data fields.

            ID NOT NULL NUMBER(10)
            SOURCE NOT NULL VARCHAR2(10)
            DEST NOT NULL VARCHAR2(40)
            OWNER NOT NULL VARCHAR2(10)
            KEY1 VARCHAR2(40)
            KEY2 VARCHAR2(40)
            KEY3 VARCHAR2(40)
            KEY4 VARCHAR2(40)
            KEY5 VARCHAR2(40)
            CONTRACT_ZIP NOT NULL LONG RAW
            SIGNATURE NOT NULL VARCHAR2(500)
            STORE_TIME NOT NULL DATE
            CERT_SERIAL_NUM NUMBER(10)

            (2) java code

            PreparedStatement stmt =
            conn.prepareStatement(
            " insert into " + tableName +
            " (id, source, dest, owner, store_time," +
            " contract_zip, signature, cert_serial_num, " +
            " key1, key2, key3, key4, key5)" +
            " values (?, ?, ?, ?, sysdate, ?, ?, ?, ?, ?, ?,
            ?, ?)"
            );


            stmt.setInt(1, e.getContractID());
            stmt.setString(2, e.getSource());
            stmt.setString(3, e.getDest());
            stmt.setString(4, e.getOwner());
            DES3Cipher encseed = new DES3Cipher(sencseed.getBytes());
            InputStream tt = new ByteArrayInputStream(encseed.encrypt(e.getZ
            ip()));
            stmt.setBinaryStream(5, tt, (encseed.encrypt(e.getZip())).length
            );
            stmt.setString(6, e.getSignature());
            stmt.setString(7, e.getCertSerialNum().toString());
            String[] keys = e.getKeys();
            String[] saveKeys = {" "," "," "," "," "};
            if (keys != null)
            for (int i=0; i<keys.length; i++) saveKeys[i] = keys;
            stmt.setString(8, saveKeys[0]);
            stmt.setString(9, saveKeys[1]);
            stmt.setString(10, saveKeys[2]);
            stmt.setString(11, saveKeys[3]);
            stmt.setString(12, saveKeys[4]);
            stmt.executeUpdate();

            (3) error

            “java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column ORA-02063: preceding line from HKUERA_AGENT”


            (4) I found that if the error appears, I need to rename the table and create a new one. Then data can be inserted into the new table.
            • 3. Re: ORA-01461 error using jdbc 10.2.0.1.0
              461997
              I think this is a bug. I use the same jdbc drivers and got same exceptions.
              Here is a test case:
              table:
              create table testtable( cola varchar2(4000),colb varchar2(20));

              java code:
              import java.io.*;
              import java.sql.*;
              import javax.naming.InitialContext;
              import javax.sql.DataSource;

              public class aatest {
              Connection conn;
              DataSource ds;
              InitialContext ctx;
              Statement st = null;
              PreparedStatement prepareQuery = null;

              public aatest()
              {
              conn = null;
              ds = null;
              ctx = null;
              st = null;
              }

              public void query(String url,String user,String passWord)
              {
              try
              {
              DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
              conn =DriverManager.getConnection(url,user,passWord);

              st =conn.createStatement();
              String sql="insert into testtable values(?,?)";
              String str="";
              prepareQuery = conn.prepareStatement(sql);
              prepareQuery.clearParameters();

              // for (int i =0;i < 500 ;i ++) works
              // for (int i =0;i < 501 ;i ++) excepiton.
              // for (int i =0;i < 1001 ;i ++) works!!!


              for (int i =0;i < 1001 ;i ++)
              { str = str+"&#22909;1";   [i] // a chinese char and a ascii char
              }

              System.out.println(str.getBytes().length);
              System.out.println(str.length());

              prepareQuery.setString(1,str);
              prepareQuery.setString(2,"test");

              prepareQuery.executeUpdate();

              st.close();
              conn.close();
              }catch(Exception e) {
              e.printStackTrace();
              }
              }

              public static void main(String[] a){
              aatest test = new aatest();
              String url = "jdbc:oracle:thin:@oracleserver:1521:orcl";
              test.query(url,"userid","passwd");
              }



              }
              • 4. Re: ORA-01461 error using jdbc 10.2.0.1.0
                Avi Abrami
                hcxclty,
                I haven't worked with the LONG RAW data-type, but let me suggest trying a BLOB instead. Perhaps check the JDBC page at the TechNet Web site.

                Good Luck,
                Avi.
                • 5. Re: ORA-01461 error using jdbc 10.2.0.1.0
                  222267
                  I encountered same error 'ORA-01461' when I update the following table.

                  SHOP VARCHAR2(250)
                  TRANSACTION VARCHAR2(250)
                  REFNO VARCHAR2(250)
                  CERTIFICATE VARCHAR2(4000)
                  EMAILADDR VARCHAR2(250)
                  ORGUNIT VARCHAR2(250)
                  DN VARCHAR2(250)
                  ISSUERDN VARCHAR2(250)
                  START_DATE VARCHAR2(250)
                  END_DATE VARCHAR2(250)
                  FINGER_PRINT VARCHAR2(250)
                  CERT_SERIAL_NUM VARCHAR2(250)
                  CERT_TYPE VARCHAR2(1)
                  CERT_DTL VARCHAR2(4000)
                  ACTIONDATE DATE


                  Interestingly, there is no 'LONG' or 'LONG RAW' field. Besides, the error
                  occurs occasionally. I wonder whether the jdbc 10x has bugs.
                  • 6. Re: ORA-01461 error using jdbc 10.2.0.1.0
                    552632
                    I have the same problem also!! I wonder it is a bug!!
                    Anyone knows the quickest way to report bugs to oracle?

                    Platform: XP SP2
                    JDK: JRockit 1.4.2 (Weblogic 8.1 SP6)
                    JDBC: ojdbc14.jar (downloaded 19Dec,2006)
                    column type: CLOB
                    middleware: hibernate 3.2 (property type="text")

                    I detected an ERROR BOUNDARY, regardless of char encodings, just ASCII text:
                    String length=1000 //ok
                    String length=1001 //ORA-01461
                    String length=2000 //ORA-01461
                    String length=2001 //ok
                    • 7. Re: ORA-01461 error using jdbc 10.2.0.1.0
                      552632
                      Hey! Are you guys using Hibernate 3?

                      The guy thinks it is the problem of oracle release2 ojdbc.jar's bughttp://forum.hibernate.org/viewtopic.php?t=964482&highlight=ora01461
                      • 8. Re: ORA-01461 error using jdbc 10.2.0.1.0
                        624971
                        Hi,
                        I am getting the same error but intermittently...
                        When I repost the data failed with this error it goes fine.
                        This error occurs only once in a while but without any pattern.
                        Is this somekind of bug?
                        What patches do I have to apply?

                        Thanks,
                        Deepti
                        • 9. Re: ORA-01461 error using jdbc 10.2.0.1.0
                          699320
                          I am facing the same issue. I am trying to run a merge statement

                          merge into member_standardized_data msd using (select ? member_id, ? member_data from dual) msds on (msd.member_id=msds.member_id) when not matched then insert (member_id, member_data) values (msds.member_id, msds.member_data) when matched then update set msd.member_data = msds.member_data

                          on the table definition

                               member_id number constraint member_standardized_data_n1 not null,
                               member_data clob constraint member_standardized_data_n2 not null,
                               txn number     

                          we are running 10G with jdbc ojdbc14-10.2.0.2.0.jar

                          The exception I see is

                          SQL state [72000]; error code [1461]; ORA-01461: can bind a LONG value only for insert into a LONG column
                          ; nested exception is java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

                          Funnily enough there is no LONG column defined in the table. I understand it has to do something with the use of dual. Any thoughts ?
                          • 10. Re: ORA-01461 error using jdbc 10.2.0.1.0
                            736191
                            Just a friendly heads up to those of you getting this message with no numeric fields in the insert statement. This error is also generated when the length of a string exceeds the columns width for a field of varchar2. In this case you can either increase the size of the varchar2 field or switch to a CLOB field.
                            • 11. Re: ORA-01461 error using jdbc 10.2.0.1.0
                              737190
                              After 3 days of deep analysis on my project, now I have a clear idea of the problem.


                              As you can find on web, newer versions of Oracle drivers for Java have the possibility to set property :
                              oracle.jdbc.RetainV9LongBindBehavior=true
                              it's considered the fix for this bug, and effectively it seems to work.

                              But also with this setting, drivers have one more bug!+

                              The problem appears in these conditions:
                              - Application tries to save a string into a LONG column database
                              - String length is more than 4000 bytes
                              - String has less than 4000 characters (it's possible in case of international characters, which takes more than 1 byte)

                              With these conditions, we have again error ORA-01461: can bind a LONG value only for insert into a LONG column.

                              It seems that drivers evaluate string length before saving it, and consider it as LONG only for 4000+ characters. The bug is just on this check, that must consider length in bytes, according to database character encoding, and not simply for character length.

                              As workaround, on my application we added a logic that appends spaces (" ") until total characters are at least 4001. In this way, string is considered correctly as LONG and storage works correctly.
                              The check is something similar to this:

                              if(value.getBytes("UTF-8").length > 4000 && value.length() < 4000)
                              for(int x = value.length(); x<=4000; x++)
                              value += " "; // always use StringBuffer for appending

                              This workaround works, but it's clear that it can be not acceptable in many cases, for application funcionalities. In my specific bug, it has been very useful.

                              My customer opened a ticket to Oracle, asking for solution of this bug. I will wait for Oracle answer.

                              Finally, we have to say that Oracle warned to not use anymore LONG datatype: it's deprecated since v8.0.

                              Bye guys ;-)

                              Edited by: user12274810 on 25-nov-2009 12.04
                              • 12. Re: ORA-01461 error using jdbc 10.2.0.1.0
                                741742
                                Hi

                                we are facing the same issue, but we can't find a way to set this property for the driver. We're using Oracle AS 10.1.3.x and there's not way to set this property for the datasource or connection pool. Also, we are using Hibernate, and adding property to the persistence.xml doesn't seem to help.

                                Can you please advise us? Thanks in advance.