How to insert a huge amout of data to oracle database?

Question

First I query some data from user A and then I need to insert all this data to user B (user in oracle I mean) and I use Java to solve it. Below is my code:

while (ds.getResultSet().next()) {
    ResultSet resultSet = ds.getResultSet();
    ResultSetMetaData metaData = resultSet.getMetaData();
    int count = metaData.getColumnCount();
    StringBuilder sb = new StringBuilder();
    sb.append("insert into ");
    sb.append(tableName.toUpperCase());
    sb.append("(");
    for (int i = 1; i <= count; i++) {
        sb.append(metaData.getColumnName(i));
        if (i != count) sb.append(",");
        else sb.append(")");
    }
    sb.append(" values(");
    for (int i = 1; i <= count; i++) {
        Object colValue = resultSet.getObject(i);
        if (colValue == null) {
            sb.append("null");
        } else {
            if (colValue instanceof Date) {
                Date d = resultSet.getDate(i);
                String dateStr = DateUtils.formatDate(d, "yyyy-MM-dd hh:mm:ss");
                sb.append("to_date(");
                sb.append("'");
                sb.append(dateStr);
                sb.append("','yyyy-MM-dd hh24:mi:ss')");
            } else {
                sb.append("'");
                sb.append(resultSet.getObject(i));
                sb.append("'");
            }
        }
        if (i != count) sb.append(",");
        else sb.append(")");
    }
    conn = datasource.getConnection();
    System.out.println(sb.toString());
    ps = conn.prepareStatement(sb.toString());
    ps.execute();
}

So it works a bit, but when it inserts above 80 records oracle just collaspses, and it's warning me in the console:

Can you guys give some advice to me about this? Thanks for your time.

Answer 1

As you guys suggest, I changed my code like below:

Connection conn = DriverManager.getConnection(url, user, pwd);         
PreparedStatement pspt = conn.prepareStatement("select * from SYS_DICT_TYPE");
ResultSet resultSet = pspt.executeQuery();                                    
ResultSetMetaData metaData = resultSet.getMetaData();                         
int columnCount = metaData.getColumnCount();                                  
StringBuilder sql = new StringBuilder();                                      
sql.append("insert into TEST_SYS (");                                         
for (int i = 1; i <= columnCount; i++) {                                      
    sql.append(metaData.getColumnName(i));                                    
    if (i != columnCount) sql.append(",");                                    
}                                                                             
sql.append(") values(");                                                      
for (int i = 1; i <= columnCount; i++) {                                      
    sql.append("?");                                                          
    if (i != columnCount) sql.append(",");                                    
}                                                                             
sql.append(")");                                                              
String s = sql.toString();                                                    

PreparedStatement pspt2 = conn.prepareStatement(s);                           
while (resultSet.next()) {                                                    
    for (int i = 1; i <= columnCount; i++) {                                  
        pspt2.setObject(i, resultSet.getObject(i));                           
    }                                                                         
    pspt2.addBatch();                                                         
}                                                                             
pspt2.executeBatch(); 

The wired thing is: At the first I build the statement end with a ';' (the sql clause should like insert into tablename values(?,?);) which cause ora-00933:SQL error.When I removed the ';' it worked perfectly well.Why could this happend?

Answer 2

Do not use Java - just do it all in Oracle.

INSERT INTO b.table_name ( col1, col2, col3, col4 )
  SELECT col1, col2, col3, col4
  FROM   a.table_name
  WHERE  some_column = some_value; -- Add filters if necessary

• You avoid having to dynamically build the query by parsing the meta-data.
• You avoid errors where you are not escaping single quotes.
  • Further to this, you avoid potential SQL Injection attacks where a user could enter something like the string value', (SELECT password_hash FROM users WHERE userid = 'Admin1' ) ) --