problem while inserting values into nestedtable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: problem while inserting values into nestedtable

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    95
    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


  2. #2
    Join Date
    Nov 2000
    Posts
    212
    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?



  3. #3
    Join Date
    Dec 2000
    Posts
    95
    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

  4. #4
    Join Date
    Dec 2000
    Posts
    95
    Hi Everybody,


    please respond to this forum

    thanks for any help

    SrinivasM

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    >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.








Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width