1 2 Previous Next 23 Replies Latest reply: Apr 29, 2013 9:28 AM by gaverill RSS

    Object types - what is Oracle's strategy

    user640107
      Most people I assume are aware of Oracle's object types on the database. In principle I think they are excellent and I would really like to take advantage of them, however as at 11.2 there exist some OOP limitations.

      The following 2 limitations specifically in my opinion preclude serious development use:

      1. No concept of private/protected/public attributes - everything is public
      2. No concept of private/protected/public methods - everything is public

      Even with these limitations I still believe they are very powerful and we will absorb their shortcomings in the short term IF_+ Oracle is intending enhancing the current implementation by implementing private/protected/public ability for object type attributes and methods.

      However I have no idea whether this will happen - so even though very appealing I currently can't commit our development to this path ....which is really frustrating.

      So, if anyone has any input on this I would love to hear it.

      Thanks in advance
        • 1. Re: Object types - what is Oracle's strategy
          sb92075
          OOP is to RDBMS; like bicycle is to fish.

          More than a decade ago Oracle offer Object Oriented Oracle (OOO).
          OOO performed as well as a brick can fly.
          So OOO was quietly no longer offered.

          HTH & YMMV!
          • 2. Re: Object types - what is Oracle's strategy
            BrendanP
            user640107 wrote:
            So, if anyone has any input on this I would love to hear it.
            Use object types as data structures to be manipulated using packages, and ignore the type body possibility.
            • 3. Re: Object types - what is Oracle's strategy
              rp0428
              >
              Even with these limitations I still believe they are very powerful and we will absorb their shortcomings in the short term IF Oracle is intending enhancing the current implementation by implementing private/protected/public ability for object type attributes and methods.
              >
              Oracle seldom provides ANY advance information about upcoming features or functionality except in their pre-release announcements, for example for the impending release of the Oracle 12c database. There has been no mention of such enhancements for 12c that I am aware of.
              >
              However I have no idea whether this will happen - so even though very appealing I currently can't commit our development to this path ....which is really frustrating.
              >
              That shouldn't be frustrating - it makes for an easy, clear-cut decision: don't use features if you need functionality that doesn't exist and isn't likely to.

              Oracle supports Java in the database and Java stored procedures and Java does support that functionality.
              • 4. Re: Object types - what is Oracle's strategy
                William Robertson
                I think most developers come to pretty much the same conclusion - for example see APC's post on Programming with Oracle SQL TYPE constructs from a couple of years ago. The rumour I heard, I forget where, was that types belong to the SQL language group within Oracle and not the PL/SQL group, and so enhancements of this sort aren't on their list.
                • 5. Re: Object types - what is Oracle's strategy
                  rp0428
                  >
                  Use object types as data structures to be manipulated using packages, and ignore the type body possibility.
                  >
                  Well that would be throwing away a powerful part of the TYPE functionality.

                  The methods in the type body can be, and often are, used to provide multi-column validation to prevent the creation of invalid instances. A simple example is an ADDRESS_TYPE (e.g. ADDRESS1, ADDRESS2, CITY, STATE, ZIP) where a valid instance must have a value for each attribute except ADDRESS2 which might be optional.

                  The constructor and the methods in the body can perform validation to ensure that the components meet certain minimum requirements: not null, length (a 1 byte address wouldn't make much sense), character or numeric content, etc. A SET_ADDRESS1 method can also perform those validations.

                  That allows instances of those types to be used by PL/SQL code without repeated validation of the attributes. I use such basic TYPEs extensively in ETL and reporting processes. Even something as simple as a TYPE that has FROM_DATE and TO_DATE benefits by having constructor and body methods that prevent the attributes from being NULL and ensure that any TO_DATE is later than the FROM_DATE.
                  • 6. Re: Object types - what is Oracle's strategy
                    BrendanP
                    Ok, I wouldn't mind such limited use of methods, although you can easily do the same in a packaged validation procedure without significant repetition - and if you're creating your own instances you may not want to validate their validity as they may be valid by construction, i.e. you know they are valid by the way they are constructed.
                    • 7. Re: Object types - what is Oracle's strategy
                      user640107
                      I'm very interested by your response - I saw the same I believe when other databases that likewise introduced the ORDBMS ability some years ago.

                      ...and I've never understood why the "OOP in the database doesn't work" sentiment is so strong.

                      I see DB applications in which I believe OOP would be extremely useful (we have one currently in which they would be extremely powerful), and where because of the data/logic separation and lack of inheritance capability in my opinion the package approach simply doesn't cut it.

                      So I would love to understand a bit more about the "why" OOP is considered so bad - could you possibly expand a bit on your rationale?

                      Thanks!
                      • 8. Re: Object types - what is Oracle's strategy
                        user640107
                        Hi there,

                        Thanks very much for your input. I'm not familiar with using Java on the database and regarding your comment:

                        "Oracle supports Java in the database and Java stored procedures and Java does support that functionality."

                        Am I right in my thinking that an important difference is that the Java object is "an in-memory object", whereas the Oracle object types can be natively stored in tables?

                        thanks again
                        • 9. Re: Object types - what is Oracle's strategy
                          sb92075
                          user640107 wrote:
                          So I would love to understand a bit more about the "why" OOP is considered so bad - could you possibly expand a bit on your rationale?
                          OOO did not scale.
                          Application response times were unacceptable.

                          You could build the most elegant & sophisticated automobile,
                          but if it got only 3 mpg & had top speed of 15 mph, you likely won't sell too many of them regardless of price.
                          • 10. Re: Object types - what is Oracle's strategy
                            user640107
                            thanks for your input,

                            I may be missing something obvious here but, in my experience, packages are a "structured programming" philosophy and as such are diametrically opposed to OOP ie. data and logic are separate / there is no instantiation / there is no inheritance etc etc.

                            If I'm right then while I'm certainly not saying object types are better than packages, I am saying that the object types add a seriously powerful arrow of alternative to the database quiver as it were.

                            And I for one would love to use them. The problem for me is that Oracle, while having created a powerful capability ....just seem to have run out of steam close to the finish.

                            (again I'm sure I'm missing something but I just don't understand why they haven't completed the encapsulation as it doesn't seem to be rocket-science to be able to "hide" a method - even public / private without protected may even be enough to get by with)


                            thanks again.
                            • 11. Re: Object types - what is Oracle's strategy
                              user640107
                              ok - great thanks, so it's a performance thing - that I can understand.

                              I do think though that there remains a place for OOP philosophy as not every implementation of a db is large and certainly our implementation could be much "cleaner" with the benefits OOP brings. I suppose it's a horses for courses thing but I absolutely take your point on the performance aspect!
                              • 12. Re: Object types - what is Oracle's strategy
                                rp0428
                                >
                                So I would love to understand a bit more about the "why" OOP is considered so bad - could you possibly expand a bit on your rationale?
                                >
                                OOP isn't 'bad' - it is just grossly inefficient. Like most everything, when used properly it is fine. But there aren't that many 'proper' uses for it in a relational database.

                                Part of the inefficiency has to do with the way the day is physically stored. An object column can be stored out-of-line in its own table. Except that if you want to modify that 'collection' you, the developer, can't do it directly by querying that out-of-line table. Oracle will query the data, make your change (e.g. delete a value) and then store the collection again.

                                You can get similar value by just creating a standard relational table (a child table) to hold the data and using a foreign key to constain the contents. Except now you CAN manipulate the CHILD table directly without even touching the parent table. You can bulk load the child table directly rather than constructing 'objects' for each row.
                                • 13. Re: Object types - what is Oracle's strategy
                                  gaverill
                                  I use objects extensively, much more now that packages, although I only store "relationally" via object views and instead-of-triggers. It takes time to get used to using these features effectively (and which ones not to use, e.g. object view hierarchies currently perform poorly, imo), but I have found that performance and scaling issues can be dealt with.

                                  Gerard
                                  • 14. Re: Object types - what is Oracle's strategy
                                    gaverill
                                    The standard reply to these criticisms is to store "relationally" and construct object-views over these relational tables. As you state, one then gets full control in deciding how to best do updates etc, depending on the nature of your data.

                                    Gerard
                                    1 2 Previous Next