Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    uk
    Posts
    3

    Red face Unanswered: Please Help Ive Been Here For Three Days

    Can anyone please show me how to insert a line into the nested table of module numbers.

    I have been trying to use this

    INSERT INTO TABLE (
    SELECT c.consists_of
    FROM course_tab c
    WHERE c.courseno = 1
    )
    SELECT 01, REF(M)
    FROM module_tab M
    WHERE M.moduleno = 215;

    where the module number is 215 and the course number is 1.

    The type and table definitions are below.

    kind regards

    Simon


    CREATE OR REPLACE TYPE course_t AS OBJECT (
    courseno NUMBER,
    class CHAR(25),
    coursename VARCHAR2 (50),
    deptno VARCHAR2 (50),
    consists_of module_list_t,
    contains student_list_t,

    MEMBER FUNCTION
    total_value RETURN NUMBER
    ) ;
    /

    Create or replace type body course_t as
    MEMBER FUNCTION total_value RETURN NUMBER IS
    total NUMBER;
    BEGIN
    total := self.contains.count;
    RETURN total;
    END;
    END;
    /

    CREATE TABLE course_tab OF course_t
    (courseno PRIMARY KEY)
    NESTED TABLE contains STORE AS student_po_line_tab,
    NESTED TABLE consists_of STORE AS module_po_line_tab;

    CREATE TABLE module_tab OF module_t
    (moduleno PRIMARY KEY,
    SCOPE FOR (is_part_of) IS course_tab
    )
    ;

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

    Re: Please Help Ive Been Here For Three Days

    I've never used all this fancy fangled nested table stuff - can't see the point really! As you have found, it's very complicated, and goes against normalisation as Codd intended...

    But shouldn't you be using some sort of CAST to the appropriate datatype here? Like (and this is pure guesswork):

    INSERT INTO TABLE (
    SELECT c.consists_of
    FROM course_tab c
    WHERE c.courseno = 1
    )
    SELECT CAST( (01, REF(M)) ) AS some_type
    FROM module_tab M
    WHERE M.moduleno = 215;

    Good luck!

  3. #3
    Join Date
    Mar 2003
    Location
    uk
    Posts
    3

    Thanks ! I'll give it a whirl

    Thanks for your help, we got kicked out of the labs today at 5 so will have to wait unti tomorrow.

    Why am I using oo ! Becuase its a final year cwk for my degree or I would be using relational.

    cheers

    Simon

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

    Re: Thanks ! I'll give it a whirl

    Originally posted by sjrowe
    Why am I using oo ! Becuase its a final year cwk for my degree or I would be using relational.
    Simon
    That makes sense! Actually, I don't mind the OO types when used as user-defined datatypes (TYPE point IS OBJECT (x NUMBER, y NUMBER) etc.) - though they don't then go far enough in my view, e.g. they don't allow you to constrain the values entered.

    But nested tables, well - that's just breaking 1st normal form isn't it?

    Anyway, hopefully someone who reads this can show the way..

  5. #5
    Join Date
    Mar 2003
    Location
    uk
    Posts
    3

    Some progress

    I eventeually managed to put a nested table inside the course table I had, it contained all the modules for the course and all the students on the course.

    But the example we were given didnt actually have a nested table rather a table of references nested inside, but we were told not to implement a nested table of references.

    This seems odd because of two things, firstly I could easily recover all from the table or a row of the table by select 8 from course_tab;
    which returned all rows and each neted table withing a row.

    But when trying to recover the module code i.e
    select * from course_tab
    where moduleno=1;

    simple enough just want everything where the module code ( nested in table) is 1 it wont do it, also I couldnt just use select * from modul_list_t;

    module_list_t is just a nested table of modul types or objects.

    the other thing is If I dont use refs and do it like that and I cant recover data in that manner it would mean duplicatin the data in their own tables ie module table, student table ( or perhaps person table with student in side with inheritence )

    Odd.

    Anyway hope I havent bored u half to death thanks for the help.

    cheers

    Simon

Posting Permissions

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