sql - how to create inheritance table in oracle

Question

there is table called event that act as parent the child inherit tables of event are special event and hotel event i have created the types as bellow but I'm contuse about how to create tables to these tables in oracle.I have referred most of the currently available solutions within Stack overflow, git hub etc. However, none of these solutions have worked out successfully.

Table types :

Event_t (
  EventID:char(5),
  EventType:varchar(20),
  VenueName:varchar(50),
  NoOfGuest:number(10)
) NOT FINAL

HotelEvent_t (
  Date:date,
  Price:numbr(8,2)
) UNDER Event_t

SpecialEvent_t (
  BookingDate:date,
  EndDate:date,
  MenuNumber:number(2), 
  Reservation ref Reservation_t
) UNDER event_t

Thank you very much and any suggestion will be greatly appreciated.

Answer 1

Create your types using the correct syntax:

CREATE TYPE Event_t AS OBJECT(
  EventID   char(5),
  EventType varchar(20),
  VenueName varchar(50),
  NoOfGuest number(10)
) NOT FINAL;

CREATE TYPE HotelEvent_t UNDER Event_t (
  datetime date,                -- Date is a keyword, try to use a different name.
  Price  number(8,2)
);

CREATE TYPE SpecialEvent_t UNDER event_t (
  BookingDate date,
  EndDate     date,
  MenuNumbers NUMBER(2),
  Reservation ref Reservation_t
);

Then you can create an object table:

CREATE TABLE Events OF Event_T(
  eventid CONSTRAINT Events__EventID__PK PRIMARY KEY
);

Then you can insert the different types into it:

INSERT INTO EVENTS VALUES(
  HotelEvent_T(
    'H1',
    'HOTEL',
    'Venue1',
    42,
    DATE '0001-02-03' + INTERVAL '04:05:06' HOUR TO SECOND,
    123456.78
  )
);

INSERT INTO EVENTS VALUES(
  SpecialEvent_T(
    'SE1',
    'SPECIAL',
    'Time Travel Convention',
    -1,
    SYSDATE,
    TRUNC(SYSDATE),
    0,
    NULL
  )
);

and get the data out of it:

SELECT e.*,
       TREAT( VALUE(e) AS HotelEvent_T ).datetime AS datetime,
       TREAT( VALUE(e) AS HotelEvent_T ).price AS price,
       TREAT( VALUE(e) AS SpecialEvent_T ).bookingdate AS bookingdate,
       TREAT( VALUE(e) AS SpecialEvent_T ).enddate AS enddate,
       TREAT( VALUE(e) AS SpecialEvent_T ).menunumbers AS menunumbers
FROM   Events e;

Which outputs:

EVENTID | EVENTTYPE | VENUENAME              | NOOFGUEST | DATETIME            |     PRICE | BOOKINGDATE         | ENDDATE             | MENUNUMBERS
:------ | :-------- | :--------------------- | --------: | :------------------ | --------: | :------------------ | :------------------ | ----------:
H1      | HOTEL     | Venue1                 |        42 | 0001-02-03 04:05:06 | 123456.78 | null                | null                |        null
SE1     | SPECIAL   | Time Travel Convention |        -1 | null                |      null | 2020-03-30 21:11:22 | 2020-03-30 00:00:00 |           0

db<>fiddle here