How to insert data in column that reference an object

Question

Below is my code

create or REPLACE type obj2 is object(e1 varchar2(20), e2 varchar2(20));
/
create table tbl2 (col1 varchar2(20), col2 ref obj2);
insert into tbl2 values('aa',obj2('aa','aa'));

I am getting below error:

Error report -
SQL Error: ORA-00932: inconsistent datatypes: expected REF OBJ2 got OBJ2
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:

Can insert be done only through PL/SQL or am I doing this in the wrong way?

Answer 1

A REFerence column needs to reference an object stored in an object-derived table.

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');

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

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;
/

Then:

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

Outputs:

COL1	COL2.E1	COL2.E2
aa	aa	aa
bbb	b	bb

fiddle