Hi Everybody,

i am facing problem while inserting the values into nested table.

first i will tell how i created the nested table


SQL> create type performance_ty as object (name varchar2(10),id varchar2(10),totalscore number);
2 /

Type created.

SQL> desc performance_ty;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NAME VARCHAR2(10)
ID VARCHAR2(10)
TOTALSCORE NUMBER


SQL> create type performance_nt as table of performance_ty;
2 /

Type created.


SQL> create table member_info (name varchar2(10),address varchar2(10),performance performance_nt)
2 nested table performance store as performance_nt_tab;

Table created.

SQL> insert into member_info values ('guenther','lbi',
2 performance_nt(
3 performance_ty('lo1','lo1',95),
4 performance_ty('lo2','lo2',94),
5 performance_ty('lo3','lo3',86),
6 performance_ty('lo4','lo4',89),
7 performance_ty('lo5','lo5',90)
8 )
9 );

1 row created.


ok now my problem starts

when inserted like following it is working

SQL> insert into table(select performance from member_info where name='guenther')
2 values (performance_ty('lo6','lo6',87));

1 row created.


but i want to insert more than one 'performance_ty' objects
i tried like this but its throwing a error

SQL> insert into table(select performance from member_info where name='guenther')
2 values (
3 performance_ty('lo6','lo6',91),
4 performance_ty('lo7','lo7',79)
5 );
insert into the(select performance from member_info where name='guenther')
*
ERROR at line 1:
ORA-00947: not enough values


if anybody have an idea about this please help me


SrinivasM