insert into column of table type which is a object of table type(nested table)

[chinnaga]

Hi ,

I have a requirment where I have to select prod list and insert into a column. I have created a table type which contains a column which is again a table type.( baiscally nested table)

somthing shown below ,
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE Student AS OBJECT ( -- create object
name VARCHAR2(25),
courses CourseList);

Can anybody please help me how to insert values into the courses using a select statment. Because values for the courses is in a differnt table and I have to insert it into this table type.
I am writing a store proc ..

PLease this is a urgent requirment.

Thanks in advance.

[Yogeeraj_1]

hi,

Additional info required:
can you please post the DDL of the courses table?

revert.

kind regards
yogeeraj

[chinnaga]

Hi,

Please find the DDL statment for courses,

create type courses is table of CourseList;

I hope this helps.

[Yogeeraj_1]

hi again,

You have created 3 user-defined types: courselist, student and courses.

I cannot see any table that would be used to store the records.

You will need to review your schema objects.

For example, after creating the table, you can issue the following command to insert a row in it.

INSERT INTO Registration_Tab
values (
1,
Student_obj('Yogeeraj D','DY19800927'),
Address_obj('777, A Street, Esperance'),
Courses_obj('AS009','MU920','DB992','SM010')
);

hope this helps!
kind regards
yogeeraj

[chinnaga]

Hi Thanks ,

I will make the changes , but I wanted to insert the data into the object in a plsql block using a select statment.

[Yogeeraj_1]

hi again,

in that case, it will be simply:
e.g.

yogi@mydb.mu>declare
2 me student;
3 begin
4 me := student('Yogeeraj',courselist('SA001','DB090','IS200'));
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
yogi@mydb.mu>


hope this helps!
kind regards
yogeeraj