Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TYPE accounttype AS OBJECT(
  no      varchar2(10),
  name    varchar2(10),
  balance number(10),
  dob date,
  member function age return number
);
CREATE TYPE BODY accounttype AS
  MEMBER FUNCTION age RETURN NUMBER
  AS
  BEGIN
    RETURN FLOOR(MONTHS_BETWEEN(sysdate,dob)/12);
  END age;
END;
/
CREATE TYPE account_branchtype AS OBJECT(
  account REF accounttype,
  branch  varchar2(10)
);
CREATE TYPE account_citytype AS OBJECT(
  account ref accounttype,
  city    varchar2(10),
  pincode number(6),
  state   varchar2(15)
);
create table account of accounttype (
  no CONSTRAINT account__no__pk PRIMARY KEY
);
create table account_branch of account_branchtype (
  account SCOPE IS account
);
create table account_city of account_citytype (
  account SCOPE IS account
);
insert into account (no, name, balance, dob)
  values('19DCS001','Rajesh','35000', DATE '2001-07-12');
1 rows affected

insert into account_branch (account, branch)
  values( (SELECT REF(a) FROM account a WHERE no = '19DCS001'),'Manjalpur');
1 rows affected

insert into account_city (account, city, pincode, state)
  values(
    (SELECT REF(a) FROM account a WHERE no = '19DCS001'),
    'Vadodara',
    '390011',
    'Gujarat'
  );
1 rows affected

SELECT a.*,
       a.age() AS age
FROM   account a;


NO
NAME
BALANCE
DOB
AGE


19DCS001
Rajesh
35000
2001-07-12 00:00:00
21


SELECT b.account.no,
       b.account.name,
       b.branch
FROM   account_branch b;


ACCOUNT.NO
ACCOUNT.NAME
BRANCH


19DCS001
Rajesh
Manjalpur


SELECT c.account.no,
       c.account.name,
       c.city,
       c.pincode,
       c.state
FROM   account_city c;


ACCOUNT.NO
ACCOUNT.NAME
CITY
PINCODE
STATE


19DCS001
Rajesh
Vadodara
390011
Gujarat