I'm getting an error

ORA-00936: missing expression

and can't figure out why.

I'm trying to create a table and add some values to it

create table flight
(
    flightId int, 
    departure VARCHAR2(15), 
    destination VARCHAR2(15), 
    dateOfFlight int, 
    timeOfFlight int
);

insert into flight (flightId, departure, destination, dateOfFlight, timeOfFlight) 
values ('SA123', 'Zurich', 'Bonn', (to_date('12/Jan/09','dd/mm/yyyy')),, '6:45');

insert into flight (flightId, departure, destination, dateOfFlight, timeOfFlight) 
values ('BA123', 'London', 'NewYork', (to_date('21/Jan/09','dd/mm/yyyy')),, '6:30');

insert into flight (flightId, departure, destination, dateOfFlight, timeOfFlight) 
values ('BA555', 'London', 'Dublin', (to_date('23/Jan/09','dd/mm/yyyy')),, '14:45');

insert into flight (flightId, departure, destination, dateOfFlight, timeOfFlight) 
values ('FA345', 'Paris', 'Zurich', (to_date('21/Jan/09','dd/mm/yyyy')),, '16:30');

select * from flight

CodePudding user response：

I see that you have both a ) and a , too much at the end. It will work if you remove them.

Please check as correct answer if you find it working.

CodePudding user response：

Did you look at what you wrote?

• obviously, two consecutive commas (as you were already told), but that's the least of your problems
• if flightid is integer, how do you plan to put SA123 into it?
• similarly, if dateofflight is integer, why did you try to insert date (suspicious, though) (to_date('12/Jan/09','dd/mm/yyyy')) into it?
  • Why suspicious? Because format model doesn't match value you're inserting. 12/Jan/09 is dd/Mon/yy, not dd/mm/yyyy.
  • Besides, be careful! Working with months' names (instead of numbers) can be tricky. For example, your Jan can't be used in my database as Jan is - I presume - English, while my database speaks Croatian. Safer option is to use 01 for January; or, provide additional parameter to to_date - nls_date_language
• the same goes for the last column, timeofflight which you declared as integer and wanted to insert 6:45 into it
• in Oracle, you shouldn't separate date and time. Use a single column whose datatype is DATE

When fixed, your code should look like this:

SQL> create table flight
  2  (
  3      flightid     varchar2(10),
  4      departure    varchar2(15),
  5      destination  varchar2(15),
  6      dateofflight date
  7  );

Table created.

SQL> insert into flight (flightid, departure, destination, dateofflight)
  2  values ('SA123', 'Zurich', 'Bonn', to_date('12/01/2009 06:45','dd/mm/yyyy hh24:mi'));

1 row created.

SQL> insert into flight (flightid, departure, destination, dateofflight)
  2  values ('BA123', 'London', 'NewYork', to_date('21/01/2009 06:30','dd/mm/yyyy hh24:mi'));

1 row created.

SQL> select * from flight;

FLIGHTID   DEPARTURE       DESTINATION     DATEOFFLIGHT
---------- --------------- --------------- ----------------
SA123      Zurich          Bonn            12/01/2009 06:45
BA123      London          NewYork         21/01/2009 06:30

SQL>

CodePudding user response：

you are trying to enter 6 values and you only have 5 in the insert or you have an extra comma :)