SQL & PL/SQL

Hi,

I am developing an Application with Oracle APEX under Oracle 19c. It is an application that allows to book a room under certains conditions.

I would like to display the records of the available rooms for a place over a certain period (ARRIVAL_DATE and DEPARTURE_DATE). I have three tables. The table ME_VOLUNTEER with the dates, and the status. The status should be “Confirmed” to allow to book a room. The table ME_VOLUNTEERS(with ARRIVAL_DATE and DEPARTURE_DATE):

CREATE TABLE "ME_VOLUNTEERS" 
  (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "NAME" VARCHAR2(100), 
    "COMMENTS" VARCHAR2(32767), 
    "VOLUNTEER_TYPE" VARCHAR2(50), 
    "STATUS" VARCHAR2(50), 
    "ARRIVAL_DATE" DATE, 
    "DEPARTURE_DATE" DATE, 
    "PLACE" VARCHAR2(50), 
    "DRIVES_" VARCHAR2(128), 
    "EMAIL" VARCHAR2(128), 
    "PHONE_NUMBER" VARCHAR2(128), 
    "COUNTRY" VARCHAR2(128), 
    "DATE_BIRTH" DATE, 
    "GENDER" VARCHAR2(128), 
    "GRADUATION_DATE" DATE, 
    "FLIGHT_DETAILS" VARCHAR2(512), 
    "AGREEMENT_SOCIAL_MEDIA" VARCHAR2(128), 
    "CREATION_DATE" DATE, 
     PRIMARY KEY ("ID")
 USING INDEX  ENABLE
  ) ;

It contains these data:

INSERT INTO E_VOLUNTEERS
(ID,NAME,COMMENTS,VOLUNTEER_TYPE,STATUS,ARRIVAL_DATE,DEPARTURE_DATE,PLACE,DRIVES_,EMAIL,PHONE_NUMBER,COUNTRY,DATE_BIRTH,GENDER,GRADUATION_DATE,FLIGHT_DETAILS,AGREEMENT_SOCIAL_MEDIA,CREATION_DATE) VALUES (1,'Name 1','Vol & Health Ed Coordinator','Coordinator','Confirmed','2026-05-01','2026-06-07','THESSALONIKI','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(2,'Name 2','Medical Coordinator','Coordinator','Confirmed','2026-04-01','2026-31-03','THESSALONIKI','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(3,'Name 3','Field coordinator','Coordinator','Unconfirmed','2026-23-02','2026-08-03','THESSALONIKI','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(4,'Name 4','reception/ coms/ logistics','Non-Medical Support','Confirmed','2025-02-12','2026-02-04','THESSALONIKI','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(5,'Name 5','senior doctor','Senior Doctor (> 3y*)','Unconfirmed','2026-02-02','2026-02-03','THESSALONIKI','No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(6,'Name 6','junior doctor + health ed','Junior Doctor','Confirmed','2026-21-01','2026-30-04','THESSALONIKI','No',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL), 
(7,'Name 7','senior doctor','Senior Doctor (> 3y*)','Confirmed','2026-03-04','2026-23-04','THESSALONIKI','Yes',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

I have the table ME_PLACES:

 CREATE TABLE "ME_PLACES" 
  (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "NAME_PLACE" VARCHAR2(128 CHAR) NOT NULL ENABLE, 
    "ADDRESS" VARCHAR2(512 CHAR), 
    "COMMENTS" VARCHAR2(512 CHAR), 
     CONSTRAINT "ME_PLACES_PK" PRIMARY KEY ("ID")
 USING INDEX  ENABLE, 
     CONSTRAINT "ME_PLACES_CON" UNIQUE ("NAME_PLACE")
 USING INDEX  ENABLE
  ) ;

It contains these data:

INSERT INTO ME_PLACES (ID,NAME_PLACE,ADDRESS,COMMENTS) VALUES (3,'REMOTE',NULL,NULL);
INSERT INTO ME_PLACES (ID,NAME_PLACE,ADDRESS,COMMENTS) VALUES (1,'THESSALONIKI',Greece,NULL);

There is the table ME_ROOM_CALENDAR:

  CREATE TABLE "ME_ROOM_CALENDAR" 
  (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "PLACE" VARCHAR2(128 CHAR) NOT NULL ENABLE, 
    "ROOM_NAME" VARCHAR2(128 CHAR) NOT NULL ENABLE, 
    "ARRIVAL_DATE" DATE NOT NULL ENABLE, 
    "DEPARTURE_DATE" DATE NOT NULL ENABLE, 
    "BOOKED__FOR__VOLUNTEER" VARCHAR2(128), 
     CONSTRAINT "ME_ROOM_CALENDAR_PK" PRIMARY KEY ("ID")
 USING INDEX  ENABLE
  ) ;

It contains these data:

INSERT INTO ME_ROOM_CALENDAR (ID,PLACE,ROOM_NAME,ARRIVAL_DATE,DEPARTURE_DATE,BOOKED__FOR__VOLUNTEER) VALUES (22,'THESSALONIKI','Room 2','2026-01-03','2026-30-07','Name 5');

There is the table ME_ROOM (it contains the name of the room, the name of the place qhere it is located, and the number of beds available for each room:

  CREATE TABLE "ME_ROOM" 
  (    "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "NAME_ROOM" VARCHAR2(128 CHAR) NOT NULL ENABLE, 
    "PLACE" VARCHAR2(128 CHAR) NOT NULL ENABLE, 
    "NUMBER_BEDS" NUMBER NOT NULL ENABLE, 
    "ADRESS" VARCHAR2(512 CHAR), 
     CONSTRAINT "ME_ROOM_PK" PRIMARY KEY ("ID")
 USING INDEX  ENABLE
  ) ;

I would like to display the records with the available rooms between ARRIVAL_DATE and DEPARTURE_DATE at a certain PLACE with the number of beds remaining (not occupied). The status of the Volunteers (in the table ME_VOULUNTEERS) should be “Confirmed”. The report would show something like that :

Headers of the report:
“Room” “Beds remainings”

Data of the report for example:
“Room 3” “2”
“Room 2” “1”

Best regards.