CODEWITHSUNDEEP
javaoracle-databaseoracle11g
Pooja Gupta
Pooja Gupta

Reputation: 11

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

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

Upvotes: 1

Views: 2150

Answers (5)

Bruno Tardin
Bruno Tardin

Reputation: 16

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.

Upvotes: 0

sol4me
sol4me

Reputation: 15698

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(";");

Upvotes: 2

Anton Zaviriukhin
Anton Zaviriukhin

Reputation: 741

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.

Upvotes: 1

Donal
Donal

Reputation: 32713

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
....;

Upvotes: 3

Joeblade
Joeblade

Reputation: 1743

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);
    }
}

Upvotes: 2

Related Questions