Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: insert the data into nested tables

    Hai
    How to insert the data into nested tables .When inserting data into nested tables i got the below error .How to drop nested tables

    create type mail_add as object(address varchar2(100));

    create type pre_add as object(prior_address mail_add);

    create type nest_addrs as table of pre_add;

    create table emp4(name varchar2(20),current_addrs mail_add,pre_add nest_addrs)
    nested table pre_add store as nested_addrs return as locator


    insert into emp4 values('MOHAN',mail_add('PLOT NO 24,PARAMILANAGAR'),NEST_ADDRS('SDDJSJ,SDBHJDB'));


    ERROR at line 1:
    ORA-00932: inconsistent datatypes


    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: insert the data into nested tables

    You need to use all the type constructors from top to bottom like this:

    insert into emp4 values
    ('MOHAN',
    mail_add('PLOT NO 24,PARAMILANAGAR'),
    NEST_ADDRS(pre_add(mail_add('SDDJSJ')),pre_add(mai l_add('SDBHJDB')))
    )


    Why are you using nested tables anyway? If it is just to see how they work, fair enough. But if this is for a real database, I wouldn't use nested tables EVER. As you can see, they are hard work to use, and they violate 1st Normal Form. If your application prefers an object view, use exactly that - a view.

  3. #3
    Join Date
    Oct 2002
    Posts
    78

    Hai

    Hai Andrews
    Thanks for your prompt reply.Nested table is used to represent a repeating group for previous addresses. Whereas a person is likely to have a small number of previous employers, most people have a larger number of previous addresses In that nested tables are used.Can you let me know if any further crons /prons about varrays also.

    Thanks in advance
    mohan

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Hai

    Originally posted by mohan
    Hai Andrews
    Thanks for your prompt reply.Nested table is used to represent a repeating group for previous addresses. Whereas a person is likely to have a small number of previous employers, most people have a larger number of previous addresses In that nested tables are used.Can you let me know if any further crons /prons about varrays also.

    Thanks in advance
    mohan
    VARRAYs would be a worse choice for something like previous address, because they have a maximum number of elements. I can only imagine using VARRAYs and nested tables for data that would NEVER be queried in an ad hoc manner, only written/read by a procedure. Not much data is like that, and certainly previous address isn't in my view.

    I would hold previous addresses in a separate (not nested) table with a foreign key to the emp table. This makes the data easily queryable in any manner, without having to "un-nest" everything. Why would you not want that?

Posting Permissions

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