Getting the “ORA-00911” error when I am trying to insert more than 1 row

Question

Lets say I have the follow insert statements.

INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');

I am using Oracle Express Edition 10g and using the web interface to execute these instructions.

If I insert them one by one, they work OK but if I enter them all at once and try to execute I get the following error.

ORA-00911: invalid character

How I can fix this issue?

Thanks.

Answer 1

I suspect you want to put the whole thing in an anonymous PL/SQL block and run that, i.e.

BEGIN
  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
END;

Depending on the tool (SQL*Plus, TOAD, iSQL*Plus, SQL Developer, and the APEX query tool may have slightly different syntax requirements), you could also add a '/' character after each INSERT statement

  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
  /