-
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
-
May be this syntax is correct (but useless):
insert into table(select performance, PERFORMANCE from member_info where name='guenther')
2 values (
3 performance_ty('lo6','lo6',91),
4 performance_ty('lo7','lo7',79)
5 );
At least in relational database you can insert only one row at a time if use "insert .... values". "insert select from " can insert many rows from subquery. Isn't?
-
Hi lnd,
there is no other alternate to insert multiple rows into a nested table with a single statement ? if no it reduces my application performance dramatically.
think that i have to insert 10 rows into the nested table , it has to hit the database(server side) 10 times.this will reduces the performance .
I know that we can insert multiple rows into the nested tables based on select queries using 'cast' and 'multiset'. but my case is different the rows which i want to insert are not retrieved from the table.
i tried with cast and multiset also ,but it didn't work.
SQL> insert into table(select performance from member_info where name='guenther')
2 values(
3 cast(
4 multiset(
5 performance_ty('lo7','lo7',78),
6 performance_ty('lo8','lo8',89)
7 ) as perfromance_nt
8 )
9 );
multiset(
*
ERROR at line 4:
ORA-00904: invalid column name
thanks for any help
SrinivasM
-
Hi Everybody,
please respond to this forum
thanks for any help
SrinivasM
-
>think that i have to insert 10 rows into the nested table , it >has to hit the database(server side) 10 times.this will >reduces the performance .
Use a stored procedure or, if number of records is fixed,
in relational manner Iwould do:
select a1 from dual
union all a2 select from dual
...
On the other hand, may be you can build a set (i.e. varray),
convert this set to a table (cast, multicast, table - I really do not find usefull specs. in standard Oracle docs about those) and then try to use "insert select"
For info. look into Oracle cartridges like SDO docs, they have some helpfull examples.
