I'm getting an error in SQL command not properly ended

Question

when I enter this

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 1, 32.5),
('123456789', 2, 7.5),
('666884444', 3, 40.0),
('453453453', 1, 20.0),
('453453453', 2, 20.0),
('333445555', 2, 10.0),
('333445555', 3, 10.0),
('333445555', 10, 10.0),
('333445555', 20, 10.0),
('999887777', 30, 30.0),
('999887777', 10, 10.0),
('987987987', 10, 35.0),
('987987987', 30, 5.0),
('987654321', 30, 20.0),
('987654321', 20, 15.0),
('888665555', 20, 0);

I get the follow error

ORA-00933: SQL command not properly ended

Offen

That's not how you do multi-row insert in Oracle, you have to use their weird multi-table insert combined with a dual select (ugh!). – Backsaw

We get this error, even when we add semicolon in the sql statement. – Dagnydago

Answer 1

In Oracle, you can't specify multiple sets of values like this (I'm guessing that you're coming from a MySQL background where that syntax is allowed). The simplest approach is to generate multiple INSERT statements

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 1, 32.5);

INSERT INTO works_on
(essn, pno, hours)
values
('123456789', 2, 7.5);

INSERT INTO works_on
(essn, pno, hours)
values
('666884444', 3, 40.0);

...

You can also insert multiple rows using a single INSERT ALL statement

INSERT ALL
  INTO works_on(essn, pno, hours) values('123456789', 1, 32.5)
  INTO works_on(essn, pno, hours) values('123456789', 2, 7.5)
  INTO works_on(essn, pno, hours) values('666884444', 3, 40.0)
  INTO works_on(essn, pno, hours) values('453453453', 1, 20.0)
  INTO works_on(essn, pno, hours) values('453453453', 2, 20.0)
  INTO works_on(essn, pno, hours) values('333445555', 2, 10.0)
  INTO works_on(essn, pno, hours) values('333445555', 3, 10.0)
  ...
SELECT *
  FROM dual;

Reject

This must be some new definition of the word "simple" of which I was previously unaware. But +1 since you're right. I should expect little more from a DBMS vendor that can't tell the difference between a NULL and an empty string :-) – Backsaw

What is this dual? – Agronomy

please explain, what is the function of word dual in this query? – Rozalie

@AnupamHaldkar - It is the name of a virtual table in Oracle that always has a single row. – Reject

If you do an INSERT ALL INTO without a SELECT afterwards, the Oracle engine will complain with a "missing SELECT keyword" error. "Select * from dual;" is a dummy select which does nothing here, except avoiding the error. – Seleta

To answer the comment "This must be some new definition of the word "simple" ": this is simpler in the sense that there is a single INSERT statement instead of many. – Seleta

Answer 2

You cannot combine all your values in a single insert like that in Oracle unfortunately. You can either separate your SQL statements, or use another approach like this to run in a single statement:

INSERT INTO works_on (essn, pno, hours)
SELECT '123456789', 1, 32.5 FROM DUAL UNION 
SELECT '123456789', 2, 7.5 FROM DUAL UNION 
SELECT '666884444', 3, 40.0 FROM DUAL UNION 
SELECT '453453453', 1, 20.0 FROM DUAL UNION 
...
SELECT '987987987', 30, 5.0 FROM DUAL UNION 
SELECT '987654321', 30, 20.0 FROM DUAL UNION 
SELECT '987654321', 20, 15.0 FROM DUAL UNION 
SELECT '888665555', 20, 0 FROM DUAL

SQL Fiddle Demo

Pytlik

Answer 3

The VALUES syntax supports inserting only one entries into a database. In the ORACLE FAQs, the following is suggested:

INSERT ALL
INTO works_on (essn, pno, hours) VALUES ('123456789', 1, 32.5)
INTO works_on (essn, pno, hours) VALUES ('123456789', 2, 7.5)
INTO works_on (essn, pno, hours) VALUES ('666884444', 3, 40.0)
INTO works_on (essn, pno, hours) VALUES ('453453453', 1, 20.0)
INTO works_on (essn, pno, hours) VALUES ('453453453', 2, 20.0)
INTO works_on (essn, pno, hours) VALUES ('333445555', 2, 10.0)
INTO works_on (essn, pno, hours) VALUES ('333445555', 3, 10.0)
INTO works_on (essn, pno, hours) VALUES ('333445555', 10, 10.0)
INTO works_on (essn, pno, hours) VALUES ('333445555', 20, 10.0)
INTO works_on (essn, pno, hours) VALUES ('999887777', 30, 30.0)
INTO works_on (essn, pno, hours) VALUES ('999887777', 10, 10.0)
INTO works_on (essn, pno, hours) VALUES ('987987987', 10, 35.0)
INTO works_on (essn, pno, hours) VALUES ('987987987', 30, 5.0)
INTO works_on (essn, pno, hours) VALUES ('987654321', 30, 20.0)
INTO works_on (essn, pno, hours) VALUES ('987654321', 20, 15.0)
INTO works_on (essn, pno, hours) VALUES ('888665555', 20, 0)
SELECT * FROM dual;

Shiva

Answer 4

try take a look at this by ShoeLace

Oracle SQL uses a semi-colon ;  as its end of statement marker.

you will need to add the ; after bother insert statments.

NB: that also assumes ADODB will allow 2 inserts in a single call.

the alternative might be to wrap both calls in a block,

    BEGIN
          insert (...) into (...);
          insert (...) into (...);
    END;

Greensward

Answer 5

INSERT INTO 
WORKS_ON VALUES
(&ESSN,&PNO,&HOURS);

After u'll get like below dat.

ENTER VALUE FOR ESSN: IN THIS U ENTER 123456789

ENTER VALUE FOR PNO: IN THIS U ENTER 1

ENTER VALUE FOR HOURS:32.5

After that You'll get:

1 ROW IS CREATED

Then type '/' symbol and press enter. You'll get for enter for insert another row into that table.

Follow above procedure for easy way to insert row into tables.

Asthenosphere

Please, there is no need to shout. And upper case text is less readable. Try to re-format it if possible – Freaky