SQL - Select instance type

Question

I am using Oracle DB and I have this type:

CREATE TYPE t_foo AS OBJECT
(a VARCHAR2(20))
NOT FINAL;

And also these subtypes

CREATE TYPE t_foo1 UNDER t_foo() NOT FINAL;
CREATE TYPE t_foo2 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;

Now my question is how can I do a query like this

SELECT a, type FROM foo;

and get the instance type of each row like ths:

__________________
| a   | type     |
__________________
| bla | t_foo1   |
| ble | t_foo2   |
| bli | t_foo1   |

Answer 1

SQL> insert into foo values(t_foo1('alpha'));
1 row inserted.

SQL> insert into foo values(t_foo2('bravo'));
1 row inserted.

SQL> insert into foo values(t_foo('charlie'));
1 row inserted.

SQL> commit;
Commit complete.

SQL> select
  f.a,
  ut.type_name as type_name
from foo f
  join user_types ut
    on ut.typeid = sys_typeid(value(f));

A                    TYPE_NAME
-------------------- ----------
alpha                T_FOO1    
bravo                T_FOO2    
charlie              T_FOO