Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    95

    Unanswered: updating data in a nested table

    Hello

    I've got a table that has a field that is a nested table. Now I want to update the data in that field and can't find any pointer to do that.

    EX.
    Code:
    CREATE OR REPLACE 
    TYPE tickets IS TABLE OF VARCHAR2(250);
    /
    
    
    CREATE TABLE personal_log
        (personal_log_id                 NUMBER(10,0) NOT NULL,
        name                       VARCHAR2(500),
        ticket                   tickets)
       NESTED TABLE ticket STORE AS ticket_tab
    /
    
    INSERT INTO personal_log (name, ticket) VALUES ("Astrid",tickets("Airplane"))
    /
    And now, I wish to add the value "Train" in the record, so that it returns me: "Astrid",("Airplane","Train")

    Thanks.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Personally, I have never found a use for nested table that couldn't be done easier and better using a daughter table.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    perhaps you should refrain from using constructs that you don't know how to control & manipulate.

    While nested objects may seem "elegant" they bring no added value to the application solution.

    KISS!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    May 2004
    Posts
    95
    just for adding some value.....

    I've sorted it out.

    create a string with all the values (comma separated), replace in the string the commas by ' comma ' and after that make an execute immediate for the command in a string concatenated with the values string.

    Hope to help someone.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Thanks, xixo, I'm sure it will be. But the best advice as others have already said is not to use nested tables in the first place! Unfortunately, since they exist and are newer than vanilla relational tables, people tend to assume they must be a Good Thing, and Oracle training courses spend a day showing you how to use them (another day, another $400!) so newbies end up using them.

    More experienced Oracle users avoid them, not because we are Luddites, but because we find that they (a) add no value, and (b) add pain and misery.

Posting Permissions

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