'ORA-01461 can bind a LONG value only for insert into a LONG column' Error Due to Oracle Database Definition
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
When restoring an XML backup, (possibly during migrations to Oracle from other DBMS) the import can fail with an ORA-01461 SQL error:
...
org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:OSPropertyText][id,10460][value,
...] (SQL Exception while executing the following:INSERT INTO propertytext (ID, propertyvalue) VALUES (?, ?) (ORA-01461: can bind a LONG value only for insert into a LONG column)
or
com.opensymphony.module.propertyset.PropertyImplementationException: while updating: [GenericEntity:OSPropertyText][value, ...]
(SQL Exception while executing the following:UPDATE propertytext SET propertyvalue=? WHERE ID=? (ORA-01461: can bind a LONG value only for insert into a LONG column ))
Cause
In fieldtype-oracle10g.xml, "very-long" is defined as a VARCHAR(4000) which often can be insufficient to store the desired information. Any entity field that is defined as such in the entitymodel.xml can potentially contain large enough data is susceptible to the problem.
Resolution
There are two possible solutions:
Modify $JIRA-Installation-Directory/atlassian-jira/
WEB-INF/classes/entitydefs/fieldtype-oracle10g.xmlto define very-long as a CLOB.OR
Increase the size of the particular field in $JIRA-Installation-Directory/
WEB-INF/classes/entitydefs/entitymodel.xmlto use extremely-long which is CLOB by default.
ℹ️ In either case, drop and re-create the database to have your JIRA applications re-populate the schema with the new definitions (or manually move the data to another table with the new definition and swap it with the previously defined object).
Was this helpful?