Oracle 18c

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 OR REPLACE TYPE Component_ty AS OBJECT (
  Code VARCHAR(10)
) NOT FINAL;
CREATE OR REPLACE TYPE External_Component_ty UNDER Component_ty(
  External_Company NUMBER
);
CREATE OR REPLACE TYPE component_ty_nt AS TABLE OF REF component_ty;
CREATE TABLE Component OF component_ty;
CREATE TABLE MODEL (
  model_name VARCHAR(20),
  model_year NUMBER,
  Component component_ty_nt,
  CONSTRAINT t_pk PRIMARY KEY (Model_Name, Model_Year)
)
NESTED TABLE Component STORE AS component_ty_nt_TAB
INSERT INTO component
SELECT external_component_ty('A', 1) FROM DUAL UNION ALL
SELECT external_component_ty('B', NULL) FROM DUAL UNION ALL
SELECT component_ty('C') FROM DUAL;
3 rows affected

INSERT INTO model
SELECT 'A', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'A')) FROM DUAL UNION ALL
SELECT 'B', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'B')) FROM DUAL UNION ALL
SELECT 'C', 1, component_ty_nt((SELECT REF(c) FROM component c WHERE code = 'C')) FROM DUAL UNION ALL
SELECT 'D', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'E', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL UNION ALL
SELECT 'F', 1, component_ty_nt(
  (SELECT REF(c) FROM component c WHERE code = 'A'),
  (SELECT REF(c) FROM component c WHERE code = 'B'),
  (SELECT REF(c) FROM component c WHERE code = 'C')
) FROM DUAL;
6 rows affected

SELECT m.model_name,
       m.model_year,
       c.code
FROM   model m
       CROSS JOIN LATERAL (
         SELECT DEREF(c.COLUMN_VALUE).code AS code
         FROM   TABLE(m.component) c
         WHERE  DEREF(c.COLUMN_VALUE) IS OF (External_Component_ty)
         AND    TREAT(DEREF(c.COLUMN_VALUE) AS External_Component_ty).External_Company IS NULL
       ) c


MODEL_NAME
MODEL_YEAR
CODE


B
1
B

E
1
B

F
1
B