You Asked
Hi,
I have five columns as below in my DB
col1 VARCHAR2(4000 CHAR)
col2 VARCHAR2(4000 CHAR)
col3 VARCHAR2(4000 CHAR)
col4 VARCHAR2(4000 CHAR)
col5 VARCHAR2(4000 CHAR)
When i try to save the data with max length, i get the following error.
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
I am using Hibernate to save the data from the application. And here is the complete message i get.
Couldnot save. Due to Hibernate flushing: could not update: [com.xyz.Data#24]; uncategorized SQLException for SQL [/* update com.xyz.DP */ update APPLICATION.TY_P set VERSION=?, CYCLE_ID=?, PL_ID=?, T_LEVEL_ID=?, TERRITORY=?, DIVISION_ID=?, DIVISION=?, LEAD=?, T_LEAD=?, STRENGTHS=?, WEAKNESSES=?, OPPORTUNITIES=?, THREATS=?, STRATEGY=?, AEVENTS=?, T_EVENTS=?, AAND_LEARNS=?, TAND_LEARNS=?, AP_EVENTS=?, TP_EVENTS=?, A_PROGRAMS=?, T_PROGRAMS=?, P_COST=?, P_BUDGET=?, LLEARNS_COST=?, L_LEARNS_BUDGET=?, PS_COST=?, PS_BUDGET=?, MROGRAMS_COST=?, MROGRAMS_BUDGET=?, STATUS=?, LAST_UPDATED_BY=?, LAST_UPDATE_DATE=? where T_ID=? and VERSION=?]; 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
It doesnt work
The strange thing is. column col5 can is accepting 4000chars and its getting saved. But if i try to save 4000 chars from Col1 through Col4 it doesnt work.
I am not sure what is the limitation on the table definition. Cant i have more than one Varchar2(4000 CHAR).
Thanks
Barath
and Tom said...
this has nothing to do with Oracle.
this has something to do with the hibernate generated code - there are no problems updating 5 varchar2(4000) columns from Java.
You can use this program to verify that it works - and this is a hibernate binding issue (that you would need to get into touch with the owners of hibernate about...)
Note: if you modify the code below to try to update with 4001 characters, it'll fail with " ORA-01461: can bind a LONG value only for insert into a LONG column" - so you might just want to check your code (eg: put a debug line of code in there to dump the lengths of the strings before the update) to ensure you are only doing 4000 or less.
import java.sql.*;
public class instest
{
static public void main(String args[]) throws Exception
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection
conn = DriverManager.getConnection
("jdbc:oracle:thin:@dellpe:1521:ora10gr2",
"scott","tiger");
conn.setAutoCommit( false );
Statement stmt = conn.createStatement();
stmt.execute(
"create table t " +
"(x number, "+
" c1 varchar2(4000),"+
" c2 varchar2(4000),"+
" c3 varchar2(4000),"+
" c4 varchar2(4000),"+
" c5 varchar2(4000) )" );
stmt.execute( "insert into t (x) values (1)" );
PreparedStatement pstmt =
conn.prepareStatement
("update t set c1 = ?, c2 = ?, c3 = ?, c4 = ?, c5 = ? where x = 1" );
String data1 = "x";
for( int i = 1; i < 4000; data1 = data1+"x", i++ );
String data2 = data1;
String data3 = data1;
String data4 = data1;
String data5 = data1;
pstmt.setString( 1, data1 );
pstmt.setString( 2, data1 );
pstmt.setString( 3, data1 );
pstmt.setString( 4, data1 );
pstmt.setString( 5, data1 );
PreparedStatement commit =
conn.prepareStatement
("commit work write immediate wait");
pstmt.executeUpdate();
commit.executeUpdate();
conn.close();
}
}
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment