here is the sample query , now how can i retrive col1 data (select col1 from below query)
select 1,2,3,cast(multiset(select col1,col2 from table A) as object type ) from table a ;
You can pass the column to the table operator:
create or replace type obj as object (
a1 int, a2 int
);
/
create or replace type obj_tab as table of obj;
/
with tab as (
select dummy,
cast (
multiset (
select 1, 2 from dual union all
select 3, 4 from dual
) as obj_tab
) o
from dual
)
select * from tab, table ( tab.o ) ;
D O(X, Y) X Y
- ------------------------------ ---------- ----------
X OBJ_TAB(OBJ(1, 2), OBJ(3, 4)) 1 2
X OBJ_TAB(OBJ(1, 2), OBJ(3, 4)) 3 4Although, this does bring the question of why you loaded it into a object type in the first place!
PS - when supplying LiveSQL links, they're supposed to reference scripts you've written to demonstrate your question. Not a random public script on there!