CODEWITHSUNDEEP
javaoracle-databasemybatis
abc
abc

Reputation: 75

Insert in database

I am using mybatis to insert the values using:

Dao.xml

<insert id="someId" parameterType="someBean" >
    INSERT ALL
     <foreach collection="list" item="item" index="index" >
    INTO table_name
               (ID,NAME)        
    VALUES
    (#{item.id},#{item.name})
   </foreach>   
   SELECT * FROM DUAL
</insert>

Dao.java

void someId(List<SomeBean> list);

List size passed is : 350

But while execution its raising an error:

org.springframework.jdbc.UncategorizedSQLException: 
 Error updating database.  Cause: java.sql.SQLException: ORA-24335: cannot support more than 1000 columns

Database used here is oracle.

Upvotes: 1

Views: 1487

Answers (2)

Pradeep Pati
Pradeep Pati

Reputation: 5919

You are using a Multi Table Insert, which is not designed to insert record into one table. Use the following instead.

<insert id="someId" parameterType="someBean" >
    INSERT INTO table_name  (ID,NAME) VALUES
     <foreach collection="list" item="item" index="index" o >              
        ( #{item.id},#{item.name})
     </foreach>   
</insert>

Upvotes: 1

APC
APC

Reputation: 146209

I asked "Does your target table really have just two columns?" and you said:

It has 20 columns

I think this is the root of the error.

The INSERT ALL statement is subject to the Oracle limit that a SQL statement can only have 1000 columns. Each INTO line has a projection and the sum of all those columns cannot exceed 1000.

Now your ibatis command will generate one INTO line for each entry in the list. 750 entries * 20 columns easily blows the 1000 column limit. Hence the error.

How to fix it?

Well in Oracle we can use the TABLE() function to turn a nested table (list) into a data source like this:

insert into table_name
select * from table (some_list) ;

Normally I would expect a call like this to be wrapped in a stored procedure. But either way, the list must fit a SQL Type (object) previously defined in the database.

So you probably do not want to go down that route, because I guess you;'re using Ibatis specifically to avoid wrangling with databse objects. But if you do decide to take that route you might find this tutorial helpful.

Otherwise, I fear you'll need to restrict the number of entries in your list to fifty. You may think this is a bit of a pain, but really ibatis is not the right tool for doing bulk uploads.

Upvotes: 1

Related Questions