Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    2

    Unanswered: Oracle object, nested table on a subtype

    Hello,

    I need som help with nested table on a subtype.
    Look at the EER model, and see what I need help with.


    So far have I got:

    // The supertype //

    create or replace type ADRESS_TP as object(
    ADRESS varchar2(50));
    /

    create or replace type ADRESSTP as table of ADRESS_TP;
    /

    create or replace type PHONE_TP as object(
    TYPE varchar2(20),
    NUMBER number);
    /

    create or replace type PHONETP as table of PHONE_TP;
    /

    create or replace WORK_TP as object(
    NAME varchar2(30),
    WORK_ADRESS ADRESSTP,
    WORK_PHONE PHONETP)
    NOT FINAL;
    /

    create table WORK_TBL of WORK_TP(
    Primary key(NAME))
    nested table ADRESS store as NT_ADR(
    (Primary key(nested_table_id, ADRESS))
    organization index compress);


    // The subtype //

    create or replace type P_ADRESS_TP as object(
    PADRESS varchar2(50));
    /
    create or replace type P_ADRESSTP as table of P_ADRESS_TP;
    /

    create or replace type PERSON_TP under WORK_TP(
    PNAME varchar2(30))
    FINAL;
    /

    As you can see I have a nested table on the subtype, and I do not know what I should do with it. Can someone please help me?

    By the way, I'm using Oracle 9i.
    Attached Thumbnails Attached Thumbnails eer.JPG  
    Last edited by jbo011; 11-20-05 at 15:56.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What do you mean by "I don't know what I should do with it"?

    I don't imagine you'll get much help here, I'm afraid. Almost nobody creates tables based on objects or with nested table columns in the "real world", because they are a ridiculous idea. You'd be much better off creating a set of normalised, "regular" relational tables really. However, I imagine you are doing this for some kind of college work and so an OO solution is demanded?

  3. #3
    Join Date
    Nov 2005
    Posts
    17
    Almost nobody creates tables based on objects or with nested table columns in the "real world", because they are a ridiculous idea.
    Wow, that's a pretty generalized statement. I guess what constitutes "real world" for some differs for others. Oracle uses nested tables and varrays in many of their applications, and I have found uses in certain situations. I have found I can do things with nested tables that is not possible with traditional relational tables without utilizing PL/SQL to enforce certain rules and configurations.

    In reality, nested tables are normalized tables that are handled by Oracle at a lower level; therefore, they have been optimized to a degree not possible from SQL. I'm not saying to drop all the relational tables in a database in favor of nested tables, but they do have their place when used in conjunction with PL/SQL. Of course they have their limitations, but to suggest their use is purely academic is a bit of a fallacy.

    Now back to the original post.

    First, there are a couple syntax problems:

    Code:
    --NUMBER is a reserved word so it cannot be an attribute name
    create or replace type PHONE_TP as object(
    TYPE varchar2(20),
    NUMBER number);
    /
    
    create or replace type PHONE_TP as object(
    TYPE varchar2(20),
    NUM number);
    /
    Code:
    -- Missing keyword TYPE
    create or replace WORK_TP as object(
    NAME varchar2(30),
    WORK_ADRESS ADRESSTP,
    WORK_PHONE PHONETP)
    NOT FINAL;
    /
    
    create or replace TYPE WORK_TP as object(
    NAME varchar2(30),
    WORK_ADRESS ADRESSTP,
    WORK_PHONE PHONETP)
    NOT FINAL;
    /
    Code:
    --Only one nested table column was specified
    create table WORK_TBL of WORK_TP(
    Primary key(NAME))
    nested table WORK_ADRESS store as NT_ADR(
    (Primary key(nested_table_id, ADRESS))
    organization index compress);
    
    -- I removed other clauses for simplicity
    create table WORK_TBL of WORK_TP(
    Primary key(NAME))
    nested table WORK_ADRESS store as NT_work_ADR
    nested table WORK_PHONE STORE AS NT_PH;
    Now you can use your object table. To understand what to do next, I described the components of the table.

    Code:
    SQL> DESC work_tbl
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     NAME                                      NOT NULL VARCHAR2(30)
     WORK_ADRESS                                        ADRESSTP
     WORK_PHONE                                         PHONETP
    
    SQL> desc ADRESSTP
     ADRESSTP TABLE OF ADRESS_TP
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ADRESS                                             VARCHAR2(50)
    
    SQL> DESC PHONETP
     PHONETP TABLE OF PHONE_TP
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     TYPE                                               VARCHAR2(20)
     NUM                                                NUMBER
    Notice in WORK_TBL there are 3 columns: NAME, WORK_ADRESS, WORK_PHONE.

    WORK_ADRESS is further comprised of a table of ADRESS_TP

    WORK_PHONE is further comprised of a table of PHONE_TP, which is made up of two attributes TYPE and NUM.

    Okay, to INSERT rows into the table perform the following:

    Code:
    INSERT INTO work_tbl (name, work_adress, work_phone)
    VALUES ('JOE'
           ,adresstp(adress_tp('123 Main Street'), adress_tp('1504 1st Avenue'))
           ,phonetp(phone_tp('OFFICE', 123456789), phone_tp('CELL', 9998887777)));
    
    INSERT INTO work_tbl (name, work_adress, work_phone)
    VALUES ('MARY'
           ,adresstp(adress_tp('905 W Jefferson Blvd'), adress_tp('1504 1st Avenue'))
           ,phonetp(phone_tp('OFFICE', 3338882222), phone_tp('CELL', 9458568888)));
    To display the contents of the table

    SELECT * FROM work_tbl;

    Code:
    NAME
    ---------------
    WORK_ADRESS(ADRESS)
    --------------------------------------------------------------------------------
    WORK_PHONE(TYPE, NUM)
    --------------------------------------------------------------------------------
    JOE
    ADRESSTP(ADRESS_TP('123 Main Street'), ADRESS_TP('1504 1st Avenue'))
    PHONETP(PHONE_TP('OFFICE', 123456789), PHONE_TP('CELL', 9998887777))
    
    MARY
    ADRESSTP(ADRESS_TP('905 W Jefferson Blvd'), ADRESS_TP('1504 1st Avenue'))
    PHONETP(PHONE_TP('OFFICE', 3338882222), PHONE_TP('CELL', 9458568888))
    This is messy to work with so unnest the nested tables using the TABLE keyword.

    Code:
    SELECT name, adr.adress, ph.type, ph.num
    FROM work_tbl, TABLE(work_adress) adr, TABLE(work_phone) ph;
    
    NAME            ADRESS               TYPE                        NUM
    --------------- -------------------- -------------------- ----------
    JOE             1504 1st Avenue      OFFICE                123456789
    JOE             123 Main Street      OFFICE                123456789
    JOE             1504 1st Avenue      CELL                 9998887777
    JOE             123 Main Street      CELL                 9998887777
    MARY            1504 1st Avenue      OFFICE               3338882222
    MARY            905 W Jefferson Blvd OFFICE               3338882222
    MARY            1504 1st Avenue      CELL                 9458568888
    MARY            905 W Jefferson Blvd CELL                 9458568888
    Hopefully that is the type of help you were requesting.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by cmerry
    Wow, that's a pretty generalized statement. I guess what constitutes "real world" for some differs for others. Oracle uses nested tables and varrays in many of their applications, and I have found uses in certain situations. I have found I can do things with nested tables that is not possible with traditional relational tables without utilizing PL/SQL to enforce certain rules and configurations.
    I'd be interested to know what sort of things they are. I'm pretty sure they will not be tables of addresses and phone numbers, for example. You could use regular relational tables and then provide Object Views to give an OO interface to the tables. That would involve a little PL/SQL in Instead Of triggers, but at least the tables would be queryable by humans also.

    Quote Originally Posted by cmerry
    In reality, nested tables are normalized tables that are handled by Oracle at a lower level; therefore, they have been optimized to a degree not possible from SQL.
    I don't know what you mean by that. Yes, they are normalised tables with a "sugar coating" of OO; I've never before heard it claimed that they are somehow better optimised - quite the reverse, in fact: they are regular tables with an overhead.
    Quote Originally Posted by cmerry
    I'm not saying to drop all the relational tables in a database in favor of nested tables, but they do have their place when used in conjunction with PL/SQL. Of course they have their limitations, but to suggest their use is purely academic is a bit of a fallacy.
    I have yet to see a non-academic example.

    Anyway, glad you are able to help the OP!

  5. #5
    Join Date
    Nov 2005
    Posts
    17
    The great thing about technology is that there will always be 100 ways to the same thing. Some like the object capabilities in Oracle, some don't. I just like to keep my mind open so I continually add more tools to my toolbox. That way I don't end up using a wrench when I should be using a hammer.

    Rather than spending the time adapting and explaining my "real world" situation to something understandable to this forum, I'll just say that I have a table of dynamic attributes. The attributes may represent numeric, character, and date values. Object tables based on various object types worked exceedingly well to hold the data, enforce data type integrity, and allow for efficient querying without the need of PL/SQL or UNIONs. Think about the alternative to objects in this case. I could create an attribute table that either had multiple columns of differing types or a single VARCHAR2 column that would hold all the datatypes forcing me, through code, to ensure dates and numbers were valid. Another approach would be to create seperate tables for each datatype, but then how do you create a list of attributes from several tables - either PL/SQL or a UNION, which generally do not offer the most efficient solution.

    As for the use of nested tables, there have been several occassions when I am working with collections in PL/SQL and it is very convenient to store the results in a single column rather than send the collection to the database in a bulk bind statement. Why seperate the values into rows if I will simply collect all the values again later using a BULK COLLECT?

    It's all about options, choices, and situations, but to say they are not used is a bit strong. I'm not really interested in getting into a debate over the practicality of the ORDBMS capabilities; I was just trying to provide information for someone that needed assistance.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by cmerry
    The great thing about technology is that there will always be 100 ways to the same thing. Some like the object capabilities in Oracle, some don't. I just like to keep my mind open so I continually add more tools to my toolbox. That way I don't end up using a wrench when I should be using a hammer.
    Me neither.
    Quote Originally Posted by cmerry
    Rather than spending the time adapting and explaining my "real world" situation to something understandable to this forum, I'll just say that I have a table of dynamic attributes. The attributes may represent numeric, character, and date values.
    Sounds like another of my pet peeves: the EAV (Entity-Attribute-Value) design. (Fabian Pascal liked my article on this so much he linked to it from his Database Debunkings site twice!)
    Quote Originally Posted by cmerry
    As for the use of nested tables, there have been several occassions when I am working with collections in PL/SQL and it is very convenient to store the results in a single column rather than send the collection to the database in a bulk bind statement. Why seperate the values into rows if I will simply collect all the values again later using a BULK COLLECT?
    Convenient as long as they only need to be accessed by your application - and not by ad hoc query tools, report writers etc.
    Quote Originally Posted by cmerry
    It's all about options, choices, and situations, but to say they are not used is a bit strong.
    I dare say they are used - and for bad reasons 90% of the time. Some people think OO means newer means better than relational, period. I can see that you are not one of these people. However, I agree 100% with Tom Kyte on OO in the database. For PL/SQL programming fine - OO is a programming paradigm first and foremost.
    Quote Originally Posted by cmerry
    I'm not really interested in getting into a debate over the practicality of the ORDBMS capabilities; I was just trying to provide information for someone that needed assistance.
    Well, you have done both - thanks!

  7. #7
    Join Date
    Nov 2005
    Posts
    17
    Well I guess we all have our opinions.

  8. #8
    Join Date
    Mar 2004
    Posts
    28
    What do you mean by "I don't know what I should do with it"?

    I don't imagine you'll get much help here, I'm afraid. Almost nobody creates tables based on objects or with nested table columns in the "real world", because they are a ridiculous idea.
    WOW! I'm shocked by your one track mind.Completely agree with cmerry.
    I havent used an object realtional schema in a while but i was well impressed with what one can do (as regards modelling real world entities) with an object relational database. As an experienced developer you should perhaps be a little bit more humane regarding people that genuinely need help, afterall isnt that what these forums are for?

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by b737
    WOW! I'm shocked by your one track mind.Completely agree with cmerry.
    I havent used an object realtional schema in a while but i was well impressed with what one can do (as regards modelling real world entities) with an object relational database. As an experienced developer you should perhaps be a little bit more humane regarding people that genuinely need help, afterall isnt that what these forums are for?
    Yes, it is. I was merely pointing out that, in my experience, questions about object-relational tables rarely get answered (actually, sometimes I answer them, but oonly if they are fairly straightforward). Maybe now that Cmerry has joined us that will change. I think if you stick around a while you will find that I am generally considered quite helpful and "humane".

    As for my "one track mind", well it's true up to a point: I have investigated OR features and found them wanting. I don't feel the need to reconsider that conclusion every time I design a database. I also feel quite strongly that it is undesirable for inexperienced developers to be gung-ho about using OR features in real databases when they are so often totally inappropriate - hence my diatribe.

    YMMV.

Posting Permissions

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