1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

abt nested table

Discussion in 'SQL PL/SQL' started by oracle123, Jan 12, 2010.

  1. oracle123

    oracle123 Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hello Friends

    i hv create object & then nested table

    CREATE OR REPLACE TYPE COURSE AS OBJECT
    (C_CODE NUMBER,
    C_CNAME VARCHAR2(10))

    CREATE TABLE STUDENT
    (
    SNO NUMBER,
    SUB COURSE)

    then,
    INSERT INTO STUDENT VALUES(COURSE(10,'ORACLE'))
    commit;

    SELECT *
    FROM STUDENT

    getting o/p
    SNO | SUB
    1 | (10,ORACLE)

    now suppose also want to add in course (20,'JAVA') WHERE SNO = 1

    so wht should i write INSERT or UPDATE ???
    & wht will b stmt ?

    Thanks...
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I am surprised that insert statement works; it should be:

    INSERT INTO STUDENT(sno, sub) VALUES(1, COURSE(10,'ORACLE'))
    commit;

    Given that the correct way to add another course would be:

    INSERT INTO STUDENT(sno, sub) VALUES(1, COURSE(20,'JAVA'))
    commit;

    since you have no primary key restricting you to unique SNO values.
     
  3. oracle123

    oracle123 Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hello..
    Thanx for reply ....

    but i want o/p

    SNO | SUB
    1 | (10,ORACLE),(20,JAVA)