Skip to Main Content
  • Questions
  • The last query gives the error "Invalid Identifier error".Please explain me whether I have refer the correct table?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Subo.

Asked: August 25, 2016 - 4:55 am UTC

Last updated: August 25, 2016 - 9:54 am UTC

Version: 10.1.2

Viewed 1000+ times

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

Comments

Subo Nan, August 26, 2016 - 4:47 am UTC