I have used this query;
select nm.* from data nm , table (nm.class) a;
Here data is my main table and class is my nested table. But the sql statement is not working.
What you are doing works, sort of:
create type my_nt_type as table of number
/
create table data (id number, class my_nt_type)
nested table class store as class_nt
/
insert into data values (1, my_nt_type(1, 2, 3, 4));
select nm.* from data nm , table (nm.class) a;
ID CLASS
---------- ------------------------------
1 MY_NT_TYPE(1, 2, 3, 4)
1 MY_NT_TYPE(1, 2, 3, 4)
1 MY_NT_TYPE(1, 2, 3, 4)
1 MY_NT_TYPE(1, 2, 3, 4)
You are cross-joining (with old syntax) the data table with table expression from table(), but you are only referring to the original data table columns in the select list; nm.* means you see all the columns from data including however your client chooses to display the nested table, with each row repeated by however many elements there are in the nested table for that row.
You can get a more useful result by specifying the non-NT columns from data, plus the columns from the exploded nested table (or fields form its object, if it's more complicated than my example):
select nm.id, a.column_value as from_class
from data nm
cross join table (nm.class) a;
ID FROM_CLASS
---------- ----------
1 1
1 2
1 3
1 4
With a table of objects it's only slightly more complicated, in that you need to list all the fields you want:
create type my_obj_type as object (field1 number, field2 varchar2(10))
/
create type my_nt_type as table of my_obj_type
/
create table data (id number, class my_nt_type)
nested table class store as class_nt
/
insert into data values (1, my_nt_type(my_obj_type(1, 'One'), my_obj_type(2, 'Two')));
insert into data values (2, my_nt_type(my_obj_type(3, 'Three'), my_obj_type(4, 'Four'),
my_obj_type(5, 'Five')));
select nm.id, a.field1, a.field2
from data nm
cross join table (nm.class) a;
ID FIELD1 FIELD2
---------- ---------- ----------
1 1 One
1 2 Two
2 3 Three
2 4 Four
2 5 Five
select ... from parent_table p inner join child_table c on <join conditions>which is much simpler to understand and query.