This discussion is archived
6 Replies Latest reply: Mar 15, 2013 9:54 AM by gaverill RSS

Dependant Inheritance

oBean Newbie
Currently Being Moderated
I'm trying out some OOP coding in PL/SQL to see if it might be worth using in a project. So far it's not looking good...

Say I have 2 objects, Organization and Department. Org looks like:

create type Organization as object (
org_type varchar2,
org_location varchar2,
depts Departments -- the departments collection (there are many departments)
) not final;

and the Department object I want to inherit the orgs attributes, and looks like:

create type Department as object under Organization (
dept_number varchar2,
dept_name varchar2
);

then the Departments collection:

create type Departments as table of Department;

Now this all fails because I can't compile Department until Organization is compiled, and I cant compile that until Departments is compiled...which cannot be created until Department exists...

I tried using a forward type definition, like so:

create type Departments;

which creates, but the Organization and Department still fail to compile.

I think I'm missing something conceptually or syntactically...not sure which...

How would such a scenario be coded?

tia. Bean
  • 1. Re: Dependant Inheritance
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    SCOTT@orcl_11gR2> drop type dpt
      2  /
    
    Type dropped.
    
    SCOTT@orcl_11gR2> drop type departments
      2  /
    
    Type dropped.
    
    SCOTT@orcl_11gR2> drop type department
      2  /
    
    Type dropped.
    
    SCOTT@orcl_11gR2> drop type organization
      2  /
    
    Type dropped.
    
    SCOTT@orcl_11gR2> create or replace type Organization as object
      2    (org_type      varchar2(15),
      3       org_location  varchar2(15))
      4    not final;
      5  /
    
    Type created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> desc organization
     organization is NOT FINAL
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ORG_TYPE                                           VARCHAR2(15)
     ORG_LOCATION                                       VARCHAR2(15)
    
    SCOTT@orcl_11gR2> create or replace type Department as object
      2    (dept_number   varchar2(15),
      3       dept_name     varchar2(15));
      4  /
    
    Type created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> desc department
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPT_NUMBER                                        VARCHAR2(15)
     DEPT_NAME                                          VARCHAR2(15)
    
    SCOTT@orcl_11gR2> create or replace type Departments
      2  as table of Department
      3  /
    
    Type created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> desc departments
     departments TABLE OF DEPARTMENT
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     DEPT_NUMBER                                        VARCHAR2(15)
     DEPT_NAME                                          VARCHAR2(15)
    
    SCOTT@orcl_11gR2> create or replace type dpt under organization
      2    (depts  departments);
      3  /
    
    Type created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> desc dpt
     dpt extends SCOTT.ORGANIZATION
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ORG_TYPE                                           VARCHAR2(15)
     ORG_LOCATION                                       VARCHAR2(15)
     DEPTS                                              DEPARTMENTS
    
    SCOTT@orcl_11gR2> 
  • 2. Re: Dependant Inheritance
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    oBean wrote:
    I'm trying out some OOP coding in PL/SQL to see if it might be worth using in a project. So far it's not looking good...
    Seems more to me like your design is not looking good.
    Say I have 2 objects, Organization and Department. Org looks like:

    create type Organization as object (
    org_type varchar2,
    org_location varchar2,
    depts Departments -- the departments collection (there are many departments)
    ) not final;

    and the Department object I want to inherit the orgs attributes, and looks like:

    create type Department as object under Organization (
    dept_number varchar2,
    dept_name varchar2
    );
    Since when does a parent class contain one of its (as yet undefined) subclasses as a property? How does this make any sense??

    Try and draw the above design in UML.

    Also keep in mind that o-o data modelling, as a substitution for a relational data model, is a very poor substitution. Yes, o-o data models seem "nice". Fact is that relational data models are better, perform faster, scale better, and provide superior data integrity.

    I would not use o-o or o-r data models in Oracle. I do however use o-o programming in PL/SQL a fair bit.
  • 3. Re: Dependant Inheritance
    oBean Newbie
    Currently Being Moderated
    Billy  Verreynne  wrote:

    Since when does a parent class contain one of its (as yet undefined) subclasses as a property? How does this make any sense??
    This [http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/10_objs.htm#5825] seems to be such a case, and similar to my case except that the Parent contains a single instance of the child, wheras mine has a collection.

    Try and draw the above design in UML.

    Also keep in mind that o-o data modelling, as a substitution for a relational data model, is a very poor substitution. Yes, o-o data models seem "nice". Fact is that relational data models are better, perform faster, scale better, and provide superior data integrity.

    I would not use o-o or o-r data models in Oracle. I do however use o-o programming in PL/SQL a fair bit.
    From what I've seen so far, I would agree with your last statement...and luckily my use case (which differs from this example I posted) is along the lines of useing PL/SQL o-o not for purely Object/Class modeling but for better organization, creating building blocks, and easier scripting of those blocks.
  • 4. Re: Dependant Inheritance
    oBean Newbie
    Currently Being Moderated
    Looks like creating yet another type (with this bit)
    Barbara Boehmer wrote:
    SCOTT@orcl_11gR2> create or replace type dpt under organization
    2    (depts  departments);
    3  /
    
    Type created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> desc dpt
    dpt extends SCOTT.ORGANIZATION
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    ORG_TYPE                                           VARCHAR2(15)
    ORG_LOCATION                                       VARCHAR2(15)
    DEPTS                                              DEPARTMENTS
    
    SCOTT@orcl_11gR2> 
    ...could be the key...thanks, I'll give this a try.
  • 5. Re: Dependant Inheritance
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    oBean wrote:
    Since when does a parent class contain one of its (as yet undefined) subclasses as a property? How does this make any sense??
    This [http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/10_objs.htm#5825] seems to be such a case, and similar to my case except that the Parent contains a single instance of the child, wheras mine has a collection.
    Still wrong from a data modelling perspective.

    I would not use o-o or o-r data models in Oracle. I do however use o-o programming in PL/SQL a fair bit.
    From what I've seen so far, I would agree with your last statement...and luckily my use case (which differs from this example I posted) is along the lines of useing PL/SQL o-o not for purely Object/Class modeling but for better organization, creating building blocks, and easier scripting of those blocks.
    Please do not use scripting and PL/SQL together - it is an insult to the PL/SQL programming language to refer to it as a scripting language. :-)

    And yes, o-o programming has a definite place in solving programming problems using the PL/SQL language. Pity that Oracle is not providing any kind of o-o framework. There are some excellent o-o class implementations (like the XMLTYPE class). And then there are some really shoddy procedural implementations (like the UTL_MAIL package). One can clearly see that development of PL/SQL interfaces and libraries and classes are done by different teams, with markedly different levels of skills and standards and experience.
  • 6. Re: Dependant Inheritance
    gaverill Journeyer
    Currently Being Moderated
    Without commenting more on the quality of your data model, you might try using references here in this approach:
    -- Forward declaration of Department
    
    create or replace type Department
    /
    
    create or replace type Departments
    is table of REF Department
    /
    
    create or replace type Organization
    is object
    (
      org_type varchar2(10),
      org_location varchar2(200),
      depts Departments -- the departments collection (there are many departments)
    )
    not final
    /
    
    create or replace type Department
    under Organization
    (
      dept_number varchar2(20),
      dept_name varchar2(50)
    )
    /
    I hope that may (belatedly) help...

    Gerard

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points