oracle-databaseoracle21c

How to insert data in column that reference an object


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?


Solution

  • 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