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 |