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 obj2 IS OBJECT(e1 varchar2(20), e2 varchar2(20));
CREATE TABLE obj2_table OF obj2;
CREATE TABLE tbl2 (
  col1 varchar2(20),
  col2 REF obj2 SCOPE IS obj2_table
);
INSERT INTO obj2_table (e1, e2) VALUES ('aa','aa');
1 rows affected

INSERT INTO tbl2 (
  col1,
  col2
) VALUES (
  'aa',
  (SELECT REF(o) FrOM obj2_table o WHERE e1 = 'aa' AND e2 = 'aa')
);
1 rows affected

DECLARE
  v_ref REF obj2;
BEGIN
  INSERT INTO obj2_table t VALUES (obj2('b','bb')) RETURNING REF(t) INTO v_ref;

  INSERT INTO tbl2 (col1, col2) VALUES ('bbb', v_ref);
END;
/
1 rows affected

SELECT t.col1,
       t.col2.e1,
       t.col2.e2
FROM   tbl2 t


COL1
COL2.E1
COL2.E2


aa
aa
aa

bbb
b
bb