Oracle 18c

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

CREATE OR REPLACE TYPE ADDRESS AS OBJECT(
    line_1 VARCHAR2 (10),
    town VARCHAR2 (10),
    postcode VARCHAR2(10)
);
CREATE OR REPLACE TYPE PROPERTY AS OBJECT(
    location ADDRESS,
    description VARCHAR2(20)
);
CREATE OR REPLACE TYPE SALE AS OBJECT(
    house PROPERTY,
    sale_price NUMBER
);
CREATE TABLE SOLD OF SALE;
INSERT INTO sold (house, sale_price)
VALUES (
  property(
    address('1 MY ROAD', 'MYTOWN', 'MY123'),
    'DESCRIPTION'
  ),
  100000
);
1 rows affected

INSERT INTO sold 
VALUES (
  sale(
    property(
      address('1 MY ROAD', 'MYTOWN', 'MY123'),
      'DESCRIPTION'
    ),
    100000
  )
);
1 rows affected

SELECT s.house.location.line_1,
       s.house.location.town,
       s.house.location.postcode,
       s.house.description,
       s.sale_price
FROM   sold s


HOUSE.LOCATION.LINE_1
HOUSE.LOCATION.TOWN
HOUSE.LOCATION.POSTCODE
HOUSE.DESCRIPTION
SALE_PRICE


1 MY ROAD
MYTOWN
MY123
DESCRIPTION
100000

1 MY ROAD
MYTOWN
MY123
DESCRIPTION
100000

HOUSE.LOCATION.LINE_1	HOUSE.LOCATION.TOWN	HOUSE.LOCATION.POSTCODE	HOUSE.DESCRIPTION	SALE_PRICE
1 MY ROAD	MYTOWN	MY123	DESCRIPTION	100000
1 MY ROAD	MYTOWN	MY123	DESCRIPTION	100000