Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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

chinnaga
Advisor

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

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.

5 REPLIES
Yogeeraj_1
Honored Contributor

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

hi,

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

revert.

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
chinnaga
Advisor

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

Hi,

Please find the DDL statment for courses,

create type courses is table of CourseList;

I hope this helps.
Yogeeraj_1
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
chinnaga
Advisor

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

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
Honored Contributor

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

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)