6 Replies Latest reply: Mar 15, 2013 11:54 AM by gaverill RSS

    Dependant Inheritance

    oBean
      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
          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
            Billy~Verreynne
            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
              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
                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
                  Billy~Verreynne
                  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
                    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