You Asked
---------------------types-------------------
create type course_ty1 as object(
cid number,
titlte varchar(15),
credits_req integer
)
/
create type unit_t as object(
ui number(5),
credit number(5)
)
/
create type offering_t as object(
unit ref unit_t,
semecter number(1),
year number(4)
)
/
create type enrolled_t as object(
uinitoffer ref offering_t,
mark number
)
/
create type ntb_ty as table of enrolled_t
/
create type student_t2 as object(
sid number(10),
name varchar(15),
phone varchar(10),
course ref course_ty1,
enrolments ntb_ty
)
/
desc student_t2
/
------------tables-------------
create table courses of course_ty1(
cid primary key
)
/
create table units of unit_t(
ui primary key
)
/
create table offered of offering_t(
unit not null references units
)
/
create table students2 of student_t2(
sid primary key,
course references courses)
nested table enrolments store as enrolledlist_ntab
/
=======================insert data============================
insert into courses values(1,'DBMS',10)
/
insert into courses values(2,'SPDC',7)
/
insert into units values(1,2)
/
insert into units values(2,5)
/
insert into units values(3,8)
/
insert into offered values((select ref(a) from units a where a.ui=1),2,2015)
/
insert into offered values((select ref(b) from units b where b.ui=2),2,2015)
/
insert into offered values((select ref(c) from units c where c.ui=3),1,2012)
/
insert into students2 values(1,'Sue','071',(select ref(a) from courses a where a.cid=1),(ntb_ty((select ref(s) from students2 s where s.enrolments.uinitoffer.unit.ui=1),80)))
/
and Chris said...
ntb_ty((select ref(s) from students2 s where s.enrolments.uinitoffer.unit.ui=1),80)
selects from the table you're inserting into. That doesn't seem right! Particularly as you haven't inserted anything yet...
I'm not sure exactly what your goal is. But selecting an offering_t ref from offered works:
insert into students2 values (
1,'Sue','071',
(select ref ( a ) from courses a where a.cid=1 ) ,
ntb_ty (
enrolled_t(
(select ref(s) from offered s where year = 2012),
80
)
)
);
select * from students2;
SID NAME PHONE COURSE ENROLMENTS
1 Sue 071 oracle.sql.REF@e55747dc oracle.sql.ARRAY@7490f06d
You can then access the elements of the enrolments by passing it as a table:
select e.uinitoffer,
e.uinitoffer.semecter,
e.uinitoffer.unit,
e.uinitoffer.unit.ui
from students2 s, table(s.ENROLMENTS) e;
UINITOFFER UINITOFFER.SEMECTER UINITOFFER.UNIT UINITOFFER.UNIT.UI
oracle.sql.REF@e55e47dc 1 oracle.sql.REF@e55b47dc 3
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment