15

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

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
UnPatoCuacCuac
  • 315
  • 2
  • 7
  • 18
  • 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!). – paxdiablo Jun 25 '13 at 03:01
  • We get this error, even when we add semicolon in the sql statement. – Sorter Dec 22 '22 at 11:11

5 Answers5

18

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;
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 3
    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 :-) – paxdiablo Jun 25 '13 at 03:04
  • 2
    What is this `dual`? – AlwaysLearning Feb 16 '18 at 12:25
  • please explain, what is the function of word ```dual``` in this query? – Anupam Haldkar Jul 19 '20 at 09:27
  • 1
    @AnupamHaldkar - It is the name of a virtual table in Oracle that always has a single row. – Justin Cave Jul 19 '20 at 12:05
  • 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. – mayeulk Jan 25 '22 at 10:44
  • 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. – mayeulk Jan 25 '22 at 10:47
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

sgeddes
  • 62,311
  • 6
  • 61
  • 83
1

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;
Miles Yucht
  • 538
  • 5
  • 12
0

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;
Community
  • 1
  • 1
Hoejhl
  • 38
  • 6
-3
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.

veljasije
  • 6,722
  • 12
  • 48
  • 79
venkat
  • 1