OracleBlog

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?

INSERT INTO table (column1, column2)
VALUES (value1, value2), (value1, value2);

ERROR at line 1:
ORA-00933: SQL command not properly ended

Unlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support multirow inserts (yet). Instead, you need to execute these as separate statements.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

Edit: Courtesy of Laurent Schneider (see the comments), here are two "tricks" in inserting several rows with the same statement.

Method #1:
INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL;

Method #2:
INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL;

The moral of the story is not to expect Oracle to comply with the ANSI/ISO SQL standard every time. In some ways, like this one, they do not comply, and of course Oracle offers many extensions to the standard.

For more information, consult Appendix B of the Oracle SQL Reference, which deals very briefly with how your version of Oracle complies with the standard.

As for the ANSI SQL standard itself, I'm not aware of any free on-line source. If you want one, you'll have to purchase a copy directly from ANSI/ISO. There are different levels of standard compliance, Oracle qualifies as entry-level compliance. I don't know exactly what standards are entry-level and which ones aren't, but apparently multi-row inserts aren't.