pl/sql block error. Cannot run INSERT query inside DECLARE block

Question

this is my create table query for Appointment table

CREATE TABLE Appointment (
  ap_id varchar(10) PRIMARY KEY, 
  ap_date Date,
  pat_id varchar(10) REFERENCES Patient(pat_id),
  doc_id varchar(10) REFERENCES Doctor(doc_id),
  rec_id varchar(10) REFERENCES Receptionist(rec_id)
);

this is my pl/sql block

DECLARE 
  ap_id Appointment.ap_id%type;
  date Appointment.ap_date%type;
  pat_id Appointment.pat_id%type;
  doc_id Appointment.doc_id%type;
  rec_id Appointment.rec_id%type;
BEGIN
  ap_id:=:appointment_id;
  date:=:appointment_date;
  pat_id:=:patient_id;
  doc_id:=:doctor_id;
  rec_id:=:Receptionist_id;

  INSERT INTO Appointment
    VALUES (ap_id,date,pat_id,doc_id,rec_id);
END;

When run it gives error

ORA-06550: line 15, column 15:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 14, column 1:
PL/SQL: SQL Statement ignored
1. DECLARE 
2. ap_id Appointment.ap_id%type;
3. date Appointment.ap_date%type;

what went wrong ???

Answer 1

The problem is that you have used a keyword for variable name. 'date' make that variable name some other and it will work.

Statements where you need to give different name

  date Appointment.ap_date%type;

and

  date:=:appointment_date;

and

  INSERT INTO Appointment
    VALUES (ap_id,date,pat_id,doc_id,rec_id);

Also as a best practice always use the columns list in INSERT statement.

INSERT INTO tbl_name (columns list separated by comma)
VALUES (value list separated by comma)

Hope it helps.