how to insert multipleinsert statements in oracle

Question

I want to execute multiple sql insert statements which are stored in string in java . Below is a string :

insert_string = "INSERT INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM');
INSERT INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM');"

I tried to execute it by batchExecute(), but it is not working for me. Please suggest what should I do??

Is the **batchExecute** call from a java library (i.e., JDBC or JPA or Hibernate etc) or is it directly to the database? — whyceewhite, Aug 12 '15 at 13:36
You might get by by wrapping the statements in a PL/SQL anonymous block, but that comes with its own restrictions. `"BEGIN INSERT ...; INSERT ...; END;` — Shannon Severance, Aug 12 '15 at 16:44

Marin · Answer 1 · 2015-08-12T19:54:17.163

Here is an edited example from: https://docs.oracle.com/javase/tutorial/jdbc/basics/retrieving.html, from the section Using Statement Objects for Batch Updates

It seems like you need to split your insert_string between batch calls as follows:

Connection con = createConnection(); // Get the connection somehow
Statement stmt = null;

try {
    con.setAutoCommit(false);
    stmt = con.createStatement();

    stmt.addBatch("INSERT INTO temp values(" +
        "    TO_DATE('12-08-15','DD/MM/RRRR') - 1, " +
        "    'rahul', 'START', '12-08-15 06:32:33.577676 PM') " );

    stmt.addBatch("INSERT INTO temp values(" +
        "    TO_DATE ('12-08-15','DD/MM/RRRR') - 1, " + 
        "    'abhishek','START','12-08-15 06:32:33.577676 PM') ");

    // ...the rest of the statements

    int [] updateCounts = stmt.executeBatch();

    con.commit(); // don't forget to call commit

 } catch(BatchUpdateException b) {
    // Handle this Batch Update Exception
 } catch(SQLException ex) {
    // Handle regular SQL Exception;
 } finally {
    // Make sure to call this.
    if (stmt != null) { stmt.close(); }
        con.setAutoCommit(true);
 }

Also I haven't checked whether `TO_DATE(...)` in the JDBC API call will work like it does in PL/SQL. You may have to manipulate the date using `java.util.Calendar` outside the SQL string. — Marin, Aug 12 '15 at 13:49
Thanks marin for your reply. but is it not possible without splitting the string?? — netdamin, Aug 12 '15 at 15:24
I tried `stmt.execute()` instead of `stmt.addBatch` for the whole set of commands as a single string, but it doesn't work. If you already have the text to be executed, and want to do it from Java, this topic may help you better: http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc — Marin, Aug 12 '15 at 19:56

score 2 · Answer 2 · edited May 23 '17 at 11:44

Various SO answers such as this one and this one imply that you can't use your current approach.

Marin's answer (which I've upvoted) shows how to batch the individual INSERT statements.

You can also do this in a single query using the INSERT ALL statement. As with Marin's answer, you'll need to alter your SQL:

INSERT ALL
  INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM')
  INTO temp values(TO_DATE ('12-08-15', 'DD/MM/RRRR') - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM')
  SELECT * FROM DUAL;";

Also, I'd avoid the RRRR format string unless you happen to be working with a non-Y2K compliant application or it's your lifelong dream to create one. Taking it a step further, you don't even need TO_DATE here because Oracle supports the ANSI date literal syntax:

INSERT ALL
  INTO temp values(DATE '2015-12-08' - 1, 'rahul', 'START', '12-08-15 06:32:33.577676 PM')
  INTO temp values(DATE '2015-12-08' - 1, 'abhishek', 'START', '12-08-15 06:32:33.577676 PM')
  SELECT * FROM DUAL;

Addendum

In case the last column being inserted is a timestamp type, I'd recommend using the ANSI timestamp literal. If you're relying on an implicit conversion using your default display format, that's a dangerous practice because someone can break your code by simply changing the default timestamp display format.

The answer updated with ANSI timestamp values goes like this (for the ANSI values you need to use a 24-hour clock):

INSERT ALL
  INTO temp values(DATE '2015-12-08' - 1, 'rahul', 'START', TIMESTAMP '2015-12-08 18:32:33.577676')
  INTO temp values(DATE '2015-12-08' - 1, 'abhishek', 'START', TIMESTAMP '2015-12-08 18:32:33.577676')
  SELECT * FROM DUAL;

Understand that when you specify date and timestamp values like this your Oracle code works every time, regardless of the current session or database date/timestamp format settings.

Of course, since you're doing this through Java, an even better approach would be to use prepared statements. They're also immune to the date/timestamp format settings, plus there's the even more important benefit of defending against SQL injection.

Those are not Oracle ANSI date literals. The date literal starts with the keyword `date` and contains no time: https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#sthref329. What you are doing is an implicit conversion using the format specification in `NLS_DATE_FORMAT`. — Shannon Severance, Aug 12 '15 at 21:48
I was referring to the first dates in each line (`DATE '2015-12-08'`), where the OP had an explicit conversion to date. I didn't bother with the timestampy-looking values because I wasn't sure if they really were a `TIMESTAMP` type - there wasn't an explicit conversion in the `INSERT` and `MM-DD-YY HH:MI:SS.FF6 AM` (or maybe even `MM-DD-RR ...`) is at best an unusual `NLS_TIMESTAMP_FORMAT`. At any rate, I appreciate you bringing this up: in case it is a timestamp I'll update my answer and explain the ANSI TIMESTAMP type as well :) — Ed Gibbs, Aug 12 '15 at 23:43
No worries - like I said, you got me thinking about my answer and I think it'll end up improved. All good :) — Ed Gibbs, Aug 12 '15 at 23:47

how to insert multipleinsert statements in oracle

2 Answers2