CODEWITHSUNDEEP
sqloracle-database
S.K
S.K

Reputation: 1

I get the following error when i try to execute my code. SQL Error: ORA-00917: missing comma

My query in Oracle keeps saying i have a missing comma error. I don't see any missing comma. Please help 

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) VALUES 
(100,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",2i,1),
(101,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",6i,2),
(102,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",10i,3),
(103,"Jessica, Janet, Robert, Jonathon","Davies, Smith, Clarkson, Campbell",14i,4),

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

Oracle does not allow the insertion of multiple rows using values. So use multiple inserts.

In addition, Oracle does not support double quotes for strings. And something like 2i doesn't make sense unless it is a string. So, the following would seem to insert the rows you want:

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (100, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '2i', 1);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (101, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '6i',2);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (102, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '10i', 3);
INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID) 
    VALUES (103, 'Jessica, Janet, Robert, Jonathon', 'Davies, Smith, Clarkson, Campbell', '14i', 4);

I find it strange that you are putting lists in a string and inserting them, but that is another matter.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can convert to select statement with union all clauses, and replace double-quotes with single-quotes, and take other non-quoted alpha-numeric values into single-quotes :

INSERT INTO TBLTEACHER (TEACHERID,FIRSTNAME,SURNAME,LOGINUMBER,COURSEID)
SELECT 100,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','2i' ,1  FROM dual UNION ALL
SELECT 101,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','6i' ,2  FROM dual UNION ALL
SELECT 102,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','10i',3  FROM dual UNION ALL
SELECT 103,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','14i',4  FROM dual;

Alternatively use INSERT ALL clause :

INSERT ALL 
  INTO TBLTEACHER VALUES(100,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','2i' ,1)
  INTO TBLTEACHER VALUES(101,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','6i' ,2)
  INTO TBLTEACHER VALUES(102,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','10i',3)
  INTO TBLTEACHER VALUES(103,'Jessica, Janet, Robert, Jonathon','Davies, Smith, Clarkson, Campbell','14i',4)
SELECT 1 FROM dual;

P.S. If number of comma-seperated components within the VALUES clause equals number of the columns of the table, then there's no need to write them explicitly.

Demo

Upvotes: 0

Related Questions