Oracle - How to cast a reference to subtype?

Question

I am using Oracle DB and I have this type with subtypes:

CREATE TYPE t_foo AS OBJECT
(id INTEGER)
NOT FINAL;

CREATE TYPE t_foo1 UNDER t_foo() NOT FINAL;

I have a table of type t_foo where I can also store subtypes of t_foo

CREATE TABLE foo OF t_foo;

INSERT INTO foo VALUES
(t_foo1(1));

Now I have a table with a column of type reference of t_foo1

CREATE TABLE bar(
myfoo1 REF t_foo1 SCOPE IS foo
);

How can I insert a reference of t_foo1 from table foo?

I tried this:

INSERT INTO bar VALUES
(select ref(f) from foo f where f.id = 1)

But it gives me a type error (expect ref t_foo1 but found t_foo)

I also tried to use the TREAT function but it doesn't work (ORA-00904: invalid identifier).

INSERT INTO bar VALUES
(select ref(t_foo1) from foo f where t_foo1.id = 1 and TREAT(t_foo1 AS t_foo));

How can I have this insert working?

Answer 1

Neither of your insert statements is correct; you're mixing values and select clauses, but modifying the first a bit gives the error you see:

insert into bar
select ref(f)
from foo f
where f.id = 1;

ORA-00932: inconsistent datatypes: expected REF MY_SCHEMA.T_FOO1 got REF MY_SCHEMA.T_FOO

I'm not quite sure how you were really trying to use treat; the closest I can guess is:

insert into bar
select ref(t_foo1)
from foo f
where t_foo1.id = 1
and treat(t_foo1 as t_foo);

but that gets ORA-00920, not ORA-00904, so that's not quite what you did still.

Anyway, this works:

insert into bar
select treat(ref(f) as ref t_foo1)
from foo f
where f.id = 1;

1 row inserted.