Getting Java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column.. While inserting the data

Question

I'm using Oracle 9.2 with Weblogic 8 server. I'm getting the Data from a table and again I'm updating into the same table with same data.

I am getting the error

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

The column is of type varchar2 (4000 bytes).

Can some one let me know why this error occurs ? Please do let me know if you like any other information. Below is my SQL Query:

/**
 * @jc:sql statement::
 * UPDATE CORRECTIVE_ACTION SET
 *     CA_ID = {dt.caId}, 
 *     CA_NBR = {dt.caNbr}, 
 *     CAPA_PLAN_ID = {dt.capaPlanId}, 
 *     OBJ_EVIDENCE_COMP = {dt.objEvidenceComp}, 
 *     APPLICABLE_ELSE_WHERE = {dt.applicableElseWhere}, 
 *     JUSTIFICATION = {dt.justification}, 
 *     MOE = {dt.moe}, 
 *     COMPLETION_DATE = {dt.completionDate}, 
 *     EXTENSION_DUE_DATE = {dt.extensionDueDate}, 
 *     STATUS_CD = {dt.statusCd},
 *     SYSTEM_STATUS_CD = {dt.systemStatusCd},  
 *     ROOT_CAUSE_CD = {dt.rootCauseCd}, 
 *     DESCRIPTION = {dt.description}, 
 *     CA_TYPE = {dt.caType}, 
 *     CREATED_BY = {dt.createdBy}, 
 *     CREATED_DATE = {dt.createdDate}, 
 *     MODIFIED_BY = {dt.modifiedBy}, 
 *     MODIFIED_DATE = {dt.modifiedDate},
 *     COMPLETION_DUE_DATE = {dt.completionDueDate}
 * WHERE CA_ID = {dt.caId}
 * ::
 */
void updateCorrectiveAction(CorrectiveActionDT dt) throws SQLException;

Answer 1

Below link might help you...

CHAR semantics and ORA-01461

Also try to search your question on Stackoverflow, you will get answers...

Answer 2

I have resolved this issue by adding : oracle.jdbc.RetainV9LongBindBehavior=true in server properties for the datasource.

Answer 3

If you are using hibernate and trying to insert a string greater than 4000 characters, it may be interpreting that as a LONG instead of a VARCHAR2 on insert. Adding a substring fixed it for us

entity.setColVal(colVal == null||colVal.length() < 4000 ? colVal : colVal.substring(0,4000));

Or you could use StringUtils.substring(colVal, 0, 4000); if you want to not have to do the null and length checks yourself.

Answer 4

When DB column size is lesser than the inserted value there is a possibility of occurring this error.