title: Casting to subtype in Oracle SQL when using refs
body: <p>I have a type <code>lecturer_typ</code> with 2 sub types <code>module_leader_typ</code> and <code>external_typ</code>.</p>
  
  <p>I have inserted objects of all 3 of these types in one table called <code>lecturer_tbl</code>.</p>
  
  <p>I want to insert a reference to a <code>module_leader_typ</code> in another table but it's telling me:</p>
  
  <pre><code>SQL Error: ORA-00932: inconsistent datatypes: expected REF 21010175.MODULE_LEADER_TYP got REF 21010175.LECTURER_TYP'
  </code></pre>
  
  <p>Here's my code:</p>
  
  <pre><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)
  );
  </code></pre>