Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type at_address_type as object(street varchar2(10), city varchar2(10), postcode varchar2(8))
/
create type at_name_type as object(title varchar2(5), firstname varchar2(10),surname varchar2(10))
/
create type at_phone_type as object (purpose varchar2(10), num varchar2(15))
/
create type at_nested_phone as table of at_phone_type
/
create type at_job_type as object (position varchar2(20))
/
create table at_job_table of at_job_type
/
insert into at_job_table values (at_job_type('team leader'));
1 rows affected

create type at_branch_type as object (branch_code number)
/
create table at_branch_table of at_branch_type
/
insert into at_branch_table values (at_branch_type(908));
1 rows affected

CREATE TYPE at_person_type AS OBJECT(
	address at_address_type,
	name at_name_type,
	phones at_nested_phone,
	ni_num VARCHAR2(5)
)NOT FINAL;
/	
CREATE TYPE at_employee_type UNDER at_person_type(
	emp_id NUMBER,
	supervisor_r REF at_employee_type,  -- NOTE supervisor is a reference to the same type
	job_r REF at_job_type,
	branch_r REF at_branch_type,
	join_date DATE
)FINAL;
/
CREATE TABLE at_employee_table of at_employee_type(
	emp_id PRIMARY KEY,
	
	CONSTRAINT check_emp_address
		CHECK (
			address.street IS NOT NULL
			AND
			address.city IS NOT NULL
			AND
			address.postcode IS NOT NULL
		),
	CONSTRAINT check_emp_name
		CHECK (
			name.title IS NOT NULL
			AND		
			name.firstname IS NOT NULL
			AND
			name.surname IS NOT NULL
		),
	CONSTRAINT check_emp_ni CHECK (ni_num IS NOT NULL),
	CONSTRAINT unique_emp_ni UNIQUE (ni_num),
	CONSTRAINT check_emp_job CHECK (job_r IS NOT NULL),
	CONSTRAINT check_emp_branch CHECK (branch_r IS NOT NULL),
	CONSTRAINT check_emp_join CHECK (join_date IS NOT NULL)
)NESTED TABLE phones STORE AS at_nested_emp_phones_storage
/
alter table at_employee_table add constraint x check (
supervisor_r.job_r.position IN ('head','manager','team leader') 
AND
supervisor_r.branch_r.branch_code = branch_r.branch_code
)
ORA-22808: REF dereferencing not allowed

CREATE OR REPLACE TRIGGER at_employee_trig
BEFORE INSERT OR UPDATE ON at_employee_table
FOR EACH ROW
DECLARE
  supervisor_job at_job_type;
  supervisor_branch at_branch_type;
  branch at_branch_type;
BEGIN
  SELECT DEREF(:new.branch_r),
    DEREF(DEREF(:new.supervisor_r).job_r),
    DEREF(DEREF(:new.supervisor_r).branch_r)
  INTO branch, supervisor_job, supervisor_branch
  FROM dual;

  IF supervisor_job.position NOT IN ('head','manager','team leader')  
  OR supervisor_branch.branch_code != branch.branch_code
  THEN
    RAISE_APPLICATION_ERROR(-20001, 'Invalid supervisor');
  END IF;
END;
/
select * from user_errors
INSERT INTO at_employee_table
SELECT at_employee_type(
    at_address_type(
        'Adam', -- street
        'Edinburgh', -- city
        'EH1 6EA' -- postcode
    ),
    at_name_type(
        'Mr', -- title
        'Jack', -- firstname
        'Smith' -- surname
    ),
    at_nested_phone(
        at_phone_type(
            'home', -- purpose
            '01311112223' -- num
        ),
        at_phone_type(
            'mobile', -- purpose
            '0781209890' -- num
        )
    ),
    'NI101', -- NI num
    101, -- emp ID
    null, -- supervisor
    REF(j), -- job
    REF(b), -- branch
    TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
)
FROM at_job_table j, at_branch_table b
WHERE j.position = 'team leader'
AND b.branch_code = 908
1 rows affected

INSERT INTO at_employee_table
SELECT at_employee_type(
    at_address_type(
        'Adam', -- street
        'Edinburgh', -- city
        'EH1 6EA' -- postcode
    ),
    at_name_type(
        'Mr', -- title
        'Jack', -- firstname
        'Smith' -- surname
    ),
    at_nested_phone(
        at_phone_type(
            'home', -- purpose
            '01311112223' -- num
        ),
        at_phone_type(
            'mobile', -- purpose
            '0781209890' -- num
        )
    ),
    'NI810', -- NI num
    804, -- emp ID
    REF(s), -- supervisor
    REF(j), -- job
    REF(b), -- branch
    TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
)
FROM at_job_table j, at_branch_table b, at_employee_table s
WHERE j.position = 'team leader'
AND b.branch_code = 908
AND s.emp_id = 101
ORA-04091: table FIDDLE_KFBYKTEXIWWDZNYINVHD.AT_EMPLOYEE_TABLE is mutating, trigger/function may not see it
ORA-06512: at "FIDDLE_KFBYKTEXIWWDZNYINVHD.AT_EMPLOYEE_TRIG", line 6
ORA-04088: error during execution of trigger 'FIDDLE_KFBYKTEXIWWDZNYINVHD.AT_EMPLOYEE_TRIG'

INSERT INTO at_employee_table (address, name, phones, ni_num, emp_id, supervisor_r, job_r, branch_r, join_date)
VALUES (
    at_address_type(
        'Adam', -- street
        'Edinburgh', -- city
        'EH1 6EA' -- postcode
    ),
    at_name_type(
        'Mr', -- title
        'Jack', -- firstname
        'Smith' -- surname
    ),
    at_nested_phone(
        at_phone_type(
            'home', -- purpose
            '01311112223' -- num
        ),
        at_phone_type(
            'mobile', -- purpose
            '0781209890' -- num
        )
    ),
    'NI810', -- NI num
    804, -- emp ID
    (SELECT REF(s) FROM at_employee_table s WHERE s.emp_id = 101),
    (SELECT REF(j) FROM at_job_table j WHERE j.position = 'team leader'),
    (SELECT REF(b) FROM at_branch_table b WHERE b.branch_code = 908),
    TO_DATE('05-Feb-18','DD-Mon-YY') -- join date
);
1 rows affected

select count(*) from at_employee_table


COUNT(*)


2