Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TYPE Reservation_T AS OBJECT(
  id NUMBER
);
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,
  Price  number(8,2)
);
CREATE TYPE SpecialEvent_t UNDER event_t (
  BookingDate date,
  EndDate     date,
  MenuNumbers  NUMber(2),
  Reservation ref Reservation_t
);
CREATE TABLE Events OF Event_T(
  eventid CONSTRAINT Events__EventID__PK PRIMARY KEY
);
INSERT INTO EVENTS VALUES(
  HotelEvent_T(
    'H1',
    'HOTEL',
    'Venue1',
    42,
    DATE '0001-02-03' + INTERVAL '04:05:06' HOUR TO SECOND,
    123456.78
  )
);
1 rows affected

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
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;


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
2025-03-17 17:22:03
2025-03-17 00:00:00
0

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	2025-03-17 17:22:03	2025-03-17 00:00:00	0