javasqlstringoracle11gora-00913

java.sql.SQLException: ORA-00913: Too many values, while trying to insert a double value into Oracle database


I have to develop a small program that inserts some data into Oracle database.

Unfortunately, I have some trouble with a SQL statement, and the execution of it. This is the code I am using:

db.execute(
    String.format("INSERT INTO tops VALUES (%d, '%s', %d, %f.00, '%s', TO_TIMESTAMP('%s', 'YYYY-MM-DD HH24:MI:SS.FF'))", 
        item.getID(),
        item.getTitle(),
        this.elements,
        item.getSize(),
        item.getEntity(),
        timestamp.toString()));

This is the part where the execution should work, but I get the following error:

java.sql.SQLException: ORA-00913: Too many values

Solution

  • You can use prepared statements like this as suggested by Guallaume on the comment;

    PreparedStatement pstmt = null;
    Connection conn = null;
    
    try{
         //if you have a method that creates a connection for you.
         conn = getConnection();
         pstmt = conn.prepareStatement("INSERT INTO tops(id, title, elements, size, entity, timeStamp) VALUES(?,?,?,?,?,?)");
         pstmt.setInt(1,item.getID());
    
         //Assuming that title is a String data type
         pstmt.setString(2,item.getTitle());
         pstmt.setString(3,this.elements);
         pstmt.setDouble(4,item.getSize()); // <--- JDBC will make sure this works
    
         //assuming Entity data type is String
         pstmt.setString(5,item.getEntity());
    
         //if your timestamp's string format is 
         //well formed, you may insert as a string.
         pstmt.setString(6,timestamp.toString());
         pstmt.executeUpdate();
    }catch(Exception e){
         e.printStackTrace();
    }finally{  
         try{
             pstmt.close();
         }catch(Exception e){}
    
         try{
             conn.close();
         }catch(Exception e){}
     }