I have a type lecturer_typ with 2 sub types module_leader_typ and external_typ.
I have inserted objects of all 3 of these types in one table called lecturer_tbl.
I want to insert a reference to a module_leader_typ in another table but it's telling me:
SQL Error: ORA-00932: inconsistent datatypes: expected REF 21010175.MODULE_LEADER_TYP got REF 21010175.LECTURER_TYP'
Here's my code:
CREATE TYPE lecturer_typ AS OBJECT
(
teacher_id NUMBER,
first_name VARCHAR2(70),
second_name VARCHAR2(70)
) NOT FINAL;
CREATE TYPE module_leader_typ UNDER lecturer_typ
(
subject_knowledge VARCHAR2(255),
contract_type VARCHAR(100),
years_experience NUMBER(3)
);
CREATE TYPE external_lecturer_typ UNDER lecturer_typ
(
institution VARCHAR2(255),
is_examiner CHAR(1) -- CONSTRAIN to 1 or 0
);
create table lecturer_tbl of lecturer_typ;
INSERT INTO lecturer_tbl VALUES( lecturer_typ(1,'jack','parrow') );
INSERT INTO lecturer_tbl VALUES( lecturer_typ(2,'liz','taylor') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(51,'noddy','friend','cambridge','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(52,'big','ears','oxford','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(53,'mike','strutter','hull','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(54,'john','ross','brunel','1') );
INSERT INTO lecturer_tbl VALUES ( external_lecturer_typ(55,'special','friend','berks','1') );
INSERT INTO lecturer_tbl VALUES ( module_leader_typ(31,'bollin','ceake','networking','full-time',30) );
INSERT INTO lecturer_tbl VALUES ( module_leader_typ(32,'biz','silovoxi','databases','full-time',15) );
INSERT INTO lecturer_tbl VALUES ( module_leader_typ(33,'tony','blair','Web Technologies','part-time',3) );
Create table module_leaders_tbl
(
the_leader REF module_leader_typ
);
insert into module_leaders_tbl VALUES
(
(SELECT REF(l) FROM lecturer_tbl l where l.teacher_id = 31)
);
You need to cast your lecturer_typ to module_leader_typ: