java.sql.SQLException: ORA-24335: cannot support more than 1000 columns

Question

I have a java program which inserts a list of store numbers with a unique ID called pilotID into an Oracle database with the following syntax:

pilotDAO.insertPilotStores(pilotID, storeList);

storeList is a List<String> storing store numbers, and pilotID is some integer like 101.

However, when the storelist is more than 999 stores, I am getting a DB exception in Oracle:

Caused by:
    java.sql.SQLException: ORA-24335: cannot support more than 1000 columns

The insert query which in use is

INSERT ALL
  INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 100, SYSDATE)
  INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 101, SYSDATE)
  INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) VALUES (96, 102, SYSDATE)
SELECT * FROM dual;

I am really stuck here. Any suggestions are welcome.

Thanks in advance
pooja

It seems to be a built-in limitation to Oracle's DB software. Maybe try flipping it so that you store to rows instead of columns? I don't really do SQL, so I don't know how you would do that, but it might be a starting point. — Nic, Dec 17 '14 at 19:54

score 3 · Answer 1 · answered Dec 17 '14 at 19:56

3

The problem is with the INSERT ALL. This feature is designed for multiple table inserts (MTI), it is not designed to insert multiple rows in one table. You can re-write your query to use single insert statements. Or you can write it like this:

INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme)
select 96, 100, SYSDATE from dual UNION ALL
select 96, 101, SYSDATE from dual UNION ALL
select 96, 102, SYSDATE from dual UNION ALL
....;

answered Dec 17 '14 at 19:56

Donal

31,121
10
63
72

how can i re-write my query for more than 1000 rows. – Pooja Gupta Dec 17 '14 at 20:22
@PoojaGupta You can use StringBuilder to re-write your query, see my answer for more details – sol4me Dec 17 '14 at 20:39
@PoojaGupta sol4me has a good solution for writing out all the sql. – Donal Dec 17 '14 at 21:35

score 2 · Answer 2 · edited May 23 '17 at 12:28

You are hitting the maximum number of columns in the database. you will most likely need to change this into a prepared statement and call it multiple times instead. each "into ...." is considered one column by oracle I would venture so if you have

INSERT ALL
  INTO eportal.pilot_store (....)
  999 more rows
SELECT * FROM dual;

then yes indeed it will not work.

instead try this (adapted from this question)

public void insertStores(int pilotId, List<Store> stores) throws SQLException { 
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = database.getConnection();
        connection.setAutoCommit(false);
        preparedStatement = connection.prepareStatement("INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_time) values (?, ?, SYSDATE)");

        // ofcourse adapt to use your own list of store id's etc.
        for (Store store : stores) {
            preparedStatement.setInt(1, pilotId);
            preparedStatement.setInt(2, store.getNumber());
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    } finally {
        close(preparedStatement);
        close(connection);
    }
}

I havent use preparedstatement batch statements before.So, if I have to insert more than 999 rows how does addbatch and executebatch will work. Please explain. — Pooja Gupta, Dec 17 '14 at 20:20
Probably one of the other answers suits better then. The above code should cover everything. otherwise you'll have to look up the relevant documentation — Joeblade, Dec 17 '14 at 20:28
I use this technique to cram in 100,000 rows at a shot and get performance values of ~ 90,000 rows per second, then again I have some very fast disks and only 1 index. — FlyingGuy, Dec 19 '14 at 06:19

sol4me · Answer 3 · 2014-12-17T20:37:03.553

As you noticed INSERT ALL doesn't performs well for big number of rows You can use UNION ALL instead

INSERT INTO
  eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) 
select 96, 100, SYSDATE from dual
union all
  select 96, 101, SYSDATE from dual
union all 
   select 96, 102, SYSDATE from dual

You can re-write you query using StringBuilder like this

StringBuilder sb = new StringBuilder("INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme) ");
String unionAll = " union all ";
for(int i = 100; i < 103; i++) {

    sb.append("select 96,").append(i).append(", SYSDATE from dual").append(unionAll);
}
sb.delete(sb.length() - unionAll.length(), sb.length());
sb.append(";");

Anton Zaviriukhin · Answer 4 · 2014-12-17T20:11:44.163

1

You may use bulk insert instead of insert all

In Java it will be something like this Oracle JDBC batching

Advantages - simple insert query, fine performance.

Using literals in query is not the right choice for Oracle (especially for queries that are run frequently) because oracle will parse query each time, and will make a mess in shared pool.

edited Dec 17 '14 at 20:11

answered Dec 17 '14 at 20:04

Anton Zaviriukhin

741
4
14

score 0 · Answer 5 · answered Jul 21 '21 at 19:38

It worked for me this way:

INSERT INTO eportal.pilot_store (pilot_id, store_nbr, last_updt_dt_tme)
        SELECT 96, 100, SYSDATE FROM dual UNION ALL
        SELECT 96, 101, SYSDATE FROM dual UNION ALL
        SELECT 96, 102, SYSDATE FROM dual;

Note that the last line should not have the "UNION ALL" sentence.

java.sql.SQLException: ORA-24335: cannot support more than 1000 columns

5 Answers5