Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 7 down vote accepted

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');
  /
share|improve this answer
    
This answer is a little bit strange. Your code example is an anonymous PL/SQL block. You cannot append a '/' or replace the ';' by a '/' after each INSERT statement. This will raise a syntax error. The block as a whole will be sent to the server. If you use sqlplus you have to append a '/' to this block so that it will be sent to the server. The second example is a list of sql statements that can be sent to the server one by one by he client. But sqlplus will send each statement two times: first after ';' and again after the '/'. – miracle173 Sep 9 '15 at 14:46
    
@miracle173 - I'm presenting two options. The first (recommended) approach is to put everything in an anonymous PL/SQL block. The second is to leave it a series of SQL statements and add a separator. As I say in my answer, whether a separator is needed will depend on the front-end tool. Since the original poster didn't specify that, given the error, I inferred that the tool (or the command being used in the tool) required the separator. – Justin Cave Sep 9 '15 at 14:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.