Oracle SQL: select from table with nested table

Question

I wonder how can i make select statement from table which have a typed column ? Type of this column is defined as:

create or replace TYPE "MYCOL" as table of MYTYPE; 
create or replace TYPE "MYTYPE" as OBJECT
( myid Number, myname Varchar2);

UPD1 Table is defined as

CREATE TABLE "T_TABLE" 
   (    "ID" NUMBER NOT NULL ENABLE, "NAME" "MYCOL" )

If i select this column with select * from T_TABLE i will get this not informative result:

1, MYSCHEMA.MYCOL([MYSCHEMA.MYTYPE],[MYSCHEMA.MYTYPE])

I want just to unwrap this types.

The display is handled by the SQL client. SQL*Plus will display this as e.g.: MYCOL(MYTYPE(1, 'somevalue')) — user330315
– user330315, Commented Dec 20, 2012 at 13:43

A.B.Cade · Accepted Answer · 2012-12-20 13:00:19Z

17

Try it like this:

select t."ID", tt.myid, tt.myname 
from "T_TABLE" t, table(t."NAME") tt;

answered Dec 20, 2012 at 13:00

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

How do you return rows from this query if there is no rows in the nested table?