java.sql.SQLException: ORA-01461: can bind a LONG value only for

Discussions

EJB programming & troubleshooting: java.sql.SQLException: ORA-01461: can bind a LONG value only for

  1. I came across java.sql.SQLException: ORA-01461: can bind a LONG value only for insert. I am using a CMP entity bean and the database access is being done through a weblogic.jdbc.oci.Driver. The application server is Weblogic 6.1.
  2. I think this is trying to tell you that you have a conflict between the type of a column in the database, and the type of a bean field in weblogic.

    Check that your types match!

    Cheers,

    Daniel.
  3. Hi Daniel,
    Thanks for your response. I think problem is not what did you mention. I am getting this error for any insert or update through a CMP, BMP or from a stateless session bean. If you have any other idea then it will be great.
    thanks
    sabir
  4. Apart from EJB inserts, this error is also thrown when database inserts are carried out using PreparedStatements. However normal SQL insert (insert into <table>…) queries succeed.

     

    The cause and solution to the issue is as follows:

    Following reference helped to solve this problem:

    Item CR60341 under the section “Known Limitations and Workarounds” in the Weblogic release notes: http://edocs.bea.com/wlp/docs40/relnotes/relnotes.htm

     

    This error occurs when there is a mismatch between the character set specification of the Oracle server and the Oracle clients. The Oracle client’s character set should be the same or subset of the Oracle server’s character set. Character sets are used to actually STORE locale-dependant data. When clients from different locales connect to the database, automatic conversion of character sets takes place in the background to display the data properly (based on the NLS_LANG setting on the client). When a client in a different locale sends data back to Oracle, same conversion takes place before data is actually stored. When the character sets on the clients and server do not match, such an error is thrown.

     

    To ensure the character set compatibility between the Oracle clients & server, the “NLS_LANG” parameter should match.

     

    The Oracle server’s NLS_LANG setting can be found out as follows:

    Fire the following query to get the full set of NLS settings of the database: SELECT * FROM NLS_DATABASE_PARAMETERS;
    The NLS_LANG parameter is <language>_<territory>. <Characterset>
    NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>

     
    The OST4 Oracle9i has been set up with UTF8 character set. Its NLS_LANG parameter is AMERICAN_AMERICA.UTF8. However the NLS_LANG parameter for the Oracle clients on dev03 or our local machine was not set with this value. On setting NLS_LANG parameter to AMERICAN_AMERICA.UTF8 on client side, the issue got resolved.
     
    To set NLS_LANG on Windows check the following entry in the registry: HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_LANG. On Unix NLS_LANG is set as a local environment variable.
  5. The problem that we are facing is when we try to update multiple records.
    The functional aspect is linking - so when we try to link multiple records together we update there ids to the one that is been raised earliast.
    Thou when we try to link a single record it works, but when we update multiple records the system either throws [java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column OR it results into updating less no of records getting updated. I am using weblogic 8.1 abd Oracle 9.1. The charset i m using on oracle are
    PARAMETER VALUE
    NLS_CHARACTERSET WE8MSWIN1252

    I have modified my m/c NLS_LANG to WE8MSWIN1252. couldn't get what more is to be done.

    I am using entity beans for updating the records which are called one at time for updating.
    Rgds
  6. select NLS_LANGUAGE.VALUE || '_' || NLS_TERRITORY.value || '.' || NLS_CHARACTERSET.value NLS_LANG
    from
    (SELECT t.value FROM NLS_DATABASE_PARAMETERS t where t.parameter='NLS_LANGUAGE') NLS_LANGUAGE,
    (SELECT t.value FROM NLS_DATABASE_PARAMETERS t where t.parameter='NLS_TERRITORY') NLS_TERRITORY,
    (SELECT t.value FROM NLS_DATABASE_PARAMETERS t where t.parameter='NLS_CHARACTERSET') NLS_CHARACTERSET
  7. check for the field match in the deployment descriptor and the same in the database table