Inconsistent data type error: expected Date got Number

Question

create table reserves(
  day date,
  sid INT,
  bid INT,
  FOREIGN KEY(sid) REFERENCES sailors(sid),
  FOREIGN KEY(bid) REFERENCES boats(bid)
);

insert into reserves values(&sid,&bid,'&date');
SQL> INSERT INTO reserves VALUES (&sid, &bid, TO_DATE('&date', 'MM/DD/YY'));
Enter value for sid: 22
Enter value for bid: 103
Enter value for date: 10/10/98
old   1: INSERT INTO reserves VALUES (&sid, &bid, TO_DATE('&date', 'MM/DD/YY'))
new   1: INSERT INTO reserves VALUES (22, 103, TO_DATE('10/10/98', 'MM/DD/YY'))
INSERT INTO reserves VALUES (22, 103, TO_DATE('10/10/98', 'MM/DD/YY'))
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Need to insert values of date, but can't. Can anyone explain what is the reason

Answer 1

Best practice is to explicitly include the identifiers for the columns you are inserting into and then you can put the values into whatever order you want:

INSERT INTO reserves (sid, bid, day) VALUES (&sid, &bid, TO_DATE('&date', 'MM/DD/YY'));

the other alternative (which is not considered best practice) is to include the columns in the same order that they were defined in the table (as the 1^st column is day, not the 3^rd):

INSERT INTO reserves VALUES (TO_DATE('&date', 'MM/DD/YY'), &sid, &bid);

Answer 2

In insert statement you have to either keep the order of columns from create table statement

INSERT INTO reserves VALUES (TO_DATE('&date', 'MM/DD/YY'), &sid, &bid);

or explicitly provide the order in insert statement

INSERT INTO reserves (sid, bid, day) VALUES (&sid, &bid, TO_DATE('&date', 'MM/DD/YY'));