"SQL command not properly ended" (Oracle SQL)

Question

So I'm trying to insert 3 rows into 2 different tables by doing this:

INSERT INTO GAMES.ATHLETE (ATHLETE_NO, ATHLETE_NAME, ATHLETE_BIRTHDATE, ATHLETE_BIRTHPLACE, ATHLETE_BORN_COUNTRY, ATHLETE_GENDER, ATHLETE_HEIGHT, ATHLETE_WEIGHT, ATHLETE__TEAM_COUNTRY)
VALUES ('12345', 'Saif Haseeb', '08-NOV-1995', 'Clayton', 'AUS', 'M', '176', '75', 'AUS'), ('12323', 'Rajandeep', '01-FEB-1995', 'Melbourne', 'AUS', 'M', '180', '77', 'AUS'), ('2818', 'Jen Selter', '18- APR-1993', 'Paris', 'FRA', 'F', '169', '63', 'FRA')
INSERT INTO GAMES.COMPETES (ATHLETE_NO, DISCIPLINE_CODE, SG_GAMENO)
VALUES ('12345', 'FB', '30'), ('12323', 'FB', '30'), ('2818', 'TT', '29')
INSERT INTO GAMES.VENUE (VENUE_NO, VENUE_NAME, VENUE_LOCATION, VENUE_USEDFROM, VENUE_USEDTO, VENUE_SEATINGCAPACITY, VENUE_STRUCTURE, VENUE_USE)
VALUES ('SY', 'SYDNEYSTADIUM', 'SYDNEY', '14-AUG-2014', '19-AUG-2014', '98172', 'A', 'Y');

However I keep getting the error in the title. Can anyone help me out here please?

• Krishnraj Rana: What is the error message ??
• user330315: You are missing the ; to terminate each statmeent.
• user330315: @AlexPoole: I disagree with that. In the list of questions on the homepage I find it very useful to be able to distinguish the different DBMS but just looking at the title.

Answer 1

Oracle doesn't let you type insert into ... values (...),(...) like PostgreSQL does you need to split it into several inserts:

INSERT INTO GAMES.ATHLETE (ATHLETE_NO, ATHLETE_NAME, ATHLETE_BIRTHDATE, ATHLETE_BIRTHPLACE, ATHLETE_BORN_COUNTRY, ATHLETE_GENDER, ATHLETE_HEIGHT, ATHLETE_WEIGHT, ATHLETE__TEAM_COUNTRY)
VALUES ('12345', 'Saif Haseeb', '08-NOV-1995', 'Clayton', 'AUS', 'M', '176', '75', 'AUS');
INSERT INTO GAMES.ATHLETE (ATHLETE_NO, ATHLETE_NAME, ATHLETE_BIRTHDATE, ATHLETE_BIRTHPLACE, ATHLETE_BORN_COUNTRY, ATHLETE_GENDER, ATHLETE_HEIGHT, ATHLETE_WEIGHT, ATHLETE__TEAM_COUNTRY) 
VALUES ('12323', 'Rajandeep', '01-FEB-1995', 'Melbourne', 'AUS', 'M', '180', '77', 'AUS'); 
INSERT INTO GAMES.ATHLETE (ATHLETE_NO, ATHLETE_NAME, ATHLETE_BIRTHDATE, ATHLETE_BIRTHPLACE, ATHLETE_BORN_COUNTRY, ATHLETE_GENDER, ATHLETE_HEIGHT, ATHLETE_WEIGHT, ATHLETE__TEAM_COUNTRY) 
VALUES ('2818', 'Jen Selter', '18- APR-1993', 'Paris', 'FRA', 'F', '169', '63', 'FRA')
INSERT INTO GAMES.COMPETES (ATHLETE_NO, DISCIPLINE_CODE, SG_GAMENO)
VALUES ('12345', 'FB', '30');
INSERT INTO GAMES.COMPETES (ATHLETE_NO, DISCIPLINE_CODE, SG_GAMENO) 
VALUES ('12323', 'FB', '30'), ('2818', 'TT', '29');
INSERT INTO GAMES.VENUE (VENUE_NO, VENUE_NAME, VENUE_LOCATION, VENUE_USEDFROM, VENUE_USEDTO, VENUE_SEATINGCAPACITY, VENUE_STRUCTURE, VENUE_USE)
VALUES ('SY', 'SYDNEYSTADIUM', 'SYDNEY', '14-AUG-2014', '19-AUG-2014', '98172', 'A', 'Y');

Answer 2

These are three Statements, but you forgot to end each with a semicolon.

Answer 3

Syntax for inserting multiple rows in Oracle

INSERT ALL
 INTO table1 (column1, column2, column3) VALUES ('val1', 'val2', 'val3')
 INTO table1 (column1, column2, column3) VALUES ('val1', 'val2', 'val3')
 INTO table1 (column1, column2, column3) VALUES ('val1', 'val2', 'val3')
SELECT * FROM dual;

Answer 4

Place Semicolon at the end of each statement.

INSERT INTO GAMES.ATHLETE (ATHLETE_NO, ATHLETE_NAME, ATHLETE_BIRTHDATE, ATHLETE_BIRTHPLACE, ATHLETE_BORN_COUNTRY, ATHLETE_GENDER, ATHLETE_HEIGHT, ATHLETE_WEIGHT, ATHLETE__TEAM_COUNTRY)
VALUES ('12345', 'Saif Haseeb', '08-NOV-1995', 'Clayton', 'AUS', 'M', '176', '75', 'AUS'), ('12323', 'Rajandeep', '01-FEB-1995', 'Melbourne', 'AUS', 'M', '180', '77', 'AUS'), ('2818', 'Jen Selter', '18- APR-1993', 'Paris', 'FRA', 'F', '169', '63', 'FRA');
INSERT INTO GAMES.COMPETES (ATHLETE_NO, DISCIPLINE_CODE, SG_GAMENO)
VALUES ('12345', 'FB', '30'), ('12323', 'FB', '30'), ('2818', 'TT', '29');
INSERT INTO GAMES.VENUE (VENUE_NO, VENUE_NAME, VENUE_LOCATION, VENUE_USEDFROM, VENUE_USEDTO, VENUE_SEATINGCAPACITY, VENUE_STRUCTURE, VENUE_USE)
VALUES ('SY', 'SYDNEYSTADIUM', 'SYDNEY', '14-AUG-2014', '19-AUG-2014', '98172', 'A', 'Y');