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?
A REF
erence 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 |