1 2 Previous Next 19 Replies Latest reply: Jul 23, 2009 11:23 AM by Hoek RSS

    plsql object type polymorphism

    713310
      Hi everyone.

      Is adhoc polymorphism possible in pl/sql using Oracle's object types? Here is some sample code that I tried on an Oracle 10.2.0.3 64-bit database running on Solaris.
      Connected.
      09:58:58 SQL> create type root_ty as object (
      09:59:03   2    a1     varchar2(32 char)
      09:59:03   3  ) not final
      09:59:03   4  /
      
      Type created.
      
      Elapsed: 00:00:00.17
      09:59:05 SQL> create type sub_ty under root_ty (
      09:59:10   2    a2     varchar2(32 char)
      09:59:10   3  , constructor function sub_ty(str in varchar2) return self as resul
      09:59:10   4  , member procedure display_a2
      09:59:10   5  ) final
      09:59:10   6  /
      
      Type created.
      
      Elapsed: 00:00:00.06
      09:59:12 SQL> create or replace type body sub_ty is
      09:59:18   2    constructor function sub_ty(str in varchar2)
      09:59:18   3      return self as result
      09:59:18   4    is
      09:59:18   5    begin
      09:59:18   6      self.a2 := str;
      09:59:18   7      
      09:59:18   8      return;
      09:59:18   9    end;
      09:59:18  10    
      09:59:18  11    member procedure display_a2
      09:59:18  12    is
      09:59:18  13    begin
      09:59:18  14      dbms_output.put_line('a2 value is .... '||a2);
      09:59:18  15    end;
      09:59:18  16  end;
      09:59:18  17  /
      
      Type body created.
      
      Elapsed: 00:00:00.04
      09:59:20 SQL> set serveroutput on
      10:00:31 SQL> declare
      10:00:35   2    l_ty    root_ty;
      10:00:35   3  begin
      10:00:35   4    l_ty := new sub_ty('Woot!');
      10:00:35   5    
      10:00:35   6    l_ty.display_a2();
      10:00:35   7  end;
      10:00:35   8  /
        l_ty.display_a2();
             *
      ERROR at line 6:
      ORA-06550: line 6, column 8:
      PLS-00302: component 'DISPLAY_A2' must be declared
      ORA-06550: line 6, column 3:
      PL/SQL: Statement ignored
      
      
      Elapsed: 00:00:00.06
      10:00:37 SQL> declare
      10:00:53   2    l_ty    root_ty;
      10:00:53   3  begin
      10:00:53   4    l_ty := new sub_ty('Woot!');
      10:00:53   5    
      10:00:53   6  --  l_ty.display_a2();
      10:00:53   7  end;
      10:00:53   8  /
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:00.01
      10:00:53 SQL> declare
      10:01:30   2    l_ty    sub_ty;
      10:01:30   3  begin
      10:01:30   4    l_ty := new sub_ty('Woot!');
      10:01:30   5    
      10:01:30   6    l_ty.display_a2();
      10:01:30   7  end;
      10:01:30   8  /
      a2 value is .... Woot!
      
      PL/SQL procedure successfully completed.
      Certainly seems like this should be possible ... Am I missing something simple?

      Thanks for any input.

      - KR
        • 1. Re: plsql object type polymorphism
          94799
          Something like this perhaps, although it may not be as elegant as you were hoping for :-D
          Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
          
          SQL> SET SERVEROUTPUT ON;
          SQL> DECLARE
            2     l_ty root_ty;
            3     l_sub_ty sub_ty;
            4  BEGIN
            5     l_ty := NEW sub_ty ('Woot!');
            6     l_sub_ty := TREAT (l_ty AS l_sub_ty);
            7     l_sub_ty.display_a2 ();
            8  END;
            9  /
          a2 value is .... Woot!
          
          PL/SQL procedure successfully completed.
          
          SQL>
          • 2. Re: plsql object type polymorphism
            Hoek
            Hi and welcome to the forum.
            Is adhoc polymorphism possible in pl/sql using Oracle's object types?
            "Polymorphism is the ability for different objects to respond differently to the same message. In object-oriented programming languages, you can define one or more methods with the same name. These methods can perform different actions and return different values."

            (http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/chone.htm#sthref41)

            I don't think it's possible in PL/SQL (couldn't find anything related to it in the docs as well, only JAVA stuff, no PL/SQL stuff)

            From your example I can't really make up any polymorphism as described above as well, but perhaps I'm missing something here ;)
            • 3. Re: plsql object type polymorphism
              Hoek
              There ya go, Hoek

              According to this Expert, my previous post is not quite right ;)
              You might like this as well:
              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:883929178230#8893634490701

              So, in short: yes, it is possible.
              • 4. Re: plsql object type polymorphism
                APC
                What Mr Kyte does is not quite the same as what the OP wants. Tom instantiates the sub-type as a sub-type and then calls the sub-type's methods. The OP wants to instantiate the sub-type as a parent type and still call the sub-type's methods. As Padders has shown, this is only possible by converting the instantiated object to the sub-type.

                The point about polymorphism is that it allows us to handle sub-types as a super-type but not vice versa. That is, we can treat both Chicken and Duck as a type of Bird and execute a fly() method, but if you want a cluck() you need a Chicken and if you want a quack() you need a Duck.

                Cheers, APC

                blog: http://radiofreetooting.blogspot.com
                • 5. Re: plsql object type polymorphism
                  Hoek
                  Thanks APC, you may have noticed I'm not really into OO-stuff that much, but I like your explanation a lot ;) and it sure helps understanding what's different in my findings!
                  Actually I was a bit surprised to find a topic on Asktom about it...
                  • 6. Re: plsql object type polymorphism
                    713310
                    Thanks Padders. I was hoping I wouldn't have to do that .... ;)
                    • 7. Re: plsql object type polymorphism
                      713310
                      Hi APC. Thanks for the response. Am I trying to do something out of the ordinary here? It seems like this is very basic functionality that should be supported.

                      If I work off your analogy, I've declare a bird, but assigned a duck to it ... I should be able to call a quack() on my bird-type variable which houses an instance of a duck.

                      What's more interesting is the following code (continuing from the code snippet in the original post):
                      11:07:08 SQL> declare
                      11:07:19   2    l_ty    root_ty;
                      11:07:19   3    l_str   varchar2(32 char);
                      11:07:19   4     
                      11:07:19   5  begin
                      11:07:19   6    l_ty := new sub_ty('Woot!');
                      11:07:19   7  
                      11:07:19   8    select u.type_name into l_str
                      11:07:19   9    from user_types u
                      11:07:19  10    where u.typeid = sys_typeid(l_ty);
                      11:07:19  11    
                      11:07:19  12    dbms_output.put_line('Type is ... '|| l_str);
                      11:07:19  13    
                      11:07:19  14  end;
                      11:07:19  15  /
                      Type is ... SUB_TY
                      
                      PL/SQL procedure successfully completed.
                      
                      Elapsed: 00:00:00.07
                      11:07:20 SQL> declare
                      11:07:43   2    l_ty    root_ty;
                      11:07:43   3    l_str   varchar2(32 char);
                      11:07:43   4     
                      11:07:43   5  begin
                      11:07:43   6    l_ty := new sub_ty('Woot!');
                      11:07:43   7  
                      11:07:43   8    select u.type_name into l_str
                      11:07:43   9    from user_types u
                      11:07:43  10    where u.typeid = sys_typeid(l_ty);
                      11:07:43  11    
                      11:07:43  12    dbms_output.put_line('Type is ... '|| l_str);
                      11:07:43  13    
                      11:07:43  14    l_ty.display_a2();
                      11:07:43  15    
                      11:07:43  16  end;
                      11:07:43  17  /
                        l_ty.display_a2();
                             *
                      ERROR at line 14:
                      ORA-06550: line 14, column 8:
                      PLS-00302: component 'DISPLAY_A2' must be declared
                      ORA-06550: line 14, column 3:
                      PL/SQL: Statement ignored
                      So it looks like Oracle is correctly calling the subtype's constructor and returns an instance of the subtype ... But it's not seeing the subtype's member method(s). Seems like a bug to me. What do you think?
                      • 8. Re: plsql object type polymorphism
                        SomeoneElse
                        we can treat both Chicken and Duck as a type of Bird and execute a fly() method
                        With god as my witness I thought turkeys could fly.
                        • 9. Re: plsql object type polymorphism
                          Hoek
                          I don't think it's a bug, simply because there's nothing documented regarding 'PL/SQL polymorphism', au contraire to 'JAVA polymorphism'.
                          But anyway: this is indeed an interesting question with some usefull stuff...
                          • 10. Re: plsql object type polymorphism
                            APC
                            If I work off your analogy, I've declare a bird, but assigned a duck to it ... I should be able to call a quack() on my bird-type
                            variable which houses an instance of a duck.
                            Sorry, but why should you? Suppose you had assigned a Chicken to a variable of type Bird: you wouldn't be able to expect to be able to call quack() then.
                            Seems like a bug to me. What do you think?
                            I don't think it's a bug. I'm not an OO expert but I think you are missing the point of polymorphism. Polymorphism allows us to define a collection of type Bird, fill it with an assortment of Chicken, Duck Hawk and Goose objects and then process them all in exactly the same way - by using the methods and attributes which are common to all of them i.e. those beloning to the Bird supertype. If the methods are overridden by the sub

                            ...

                            dang it! I'm going to have to scare up an example. Back soon!

                            Cheers, APC

                            blog: http://radiofreetooting.blogspot.com
                            • 11. Re: plsql object type polymorphism
                              APC
                              Here is an example of what polymorphism is supposed to do:
                              SQL> create type bird as object
                                2  (name varchar2 (30)
                                3  , member function fly return varchar2
                                4  , member function make_a_noise return varchar2)
                                5  not final
                                6  /
                              
                              Type created.
                              
                              SQL>
                              SQL> create type birds_nt as table of bird;
                                2  /
                              
                              Type created.
                              
                              SQL>
                              SQL>
                              SQL> create or replace type body bird as
                                2
                                3      member function fly return varchar2
                                4      is
                                5      begin
                                6          return 'my name is '||self.name;
                                7      end;
                                8
                                9      member function make_a_noise return varchar2
                               10      is
                               11      begin
                               12          return 'generic twitter';
                               13      end;
                               14
                               15  end;
                               16  /
                              
                              Type body created.
                              
                              SQL>
                              SQL>
                              SQL> create type duck under bird (
                                2  overriding member function make_a_noise return varchar2
                                3  , member procedure waddle
                                4  );
                                5  /
                              
                              Type created.
                              
                              SQL>
                              SQL> create or replace type body duck as
                                2
                                3
                                4      overriding member function make_a_noise return varchar2
                                5      is
                                6      begin
                                7          return 'quack!';
                                8      end;
                                9
                               10      member procedure waddle
                               11      is
                               12      begin
                               13          null;
                               14      end;
                               15
                               16
                               17  end;
                               18  /
                              
                              Type body created.
                              
                              SQL>
                              SQL>
                              SQL>
                              SQL> create type chicken under bird (
                                2  overriding member function make_a_noise return varchar2
                                3  , member procedure peck
                                4  )
                                5  not final;
                                6  /
                              
                              Type created.
                              
                              SQL>
                              SQL> create or replace type body chicken as
                                2
                                3
                                4      overriding member function make_a_noise return varchar2
                                5      is
                                6      begin
                                7          return 'cluck!';
                                8      end;
                                9
                               10      member procedure peck
                               11      is
                               12      begin
                               13          null;
                               14      end;
                               15
                               16
                               17  end;
                               18  /
                              
                              Type body created.
                              
                              SQL>
                              SQL> create type canary under bird (
                                2  overriding member function make_a_noise return varchar2
                                3  );
                                4  /
                              
                              Type created.
                              
                              SQL>
                              SQL> create or replace type body canary as
                                2
                                3
                                4      overriding member function make_a_noise return varchar2
                                5      is
                                6      begin
                                7          return 'I taught I taw a puddy tat!!!';
                                8      end;
                                9
                               10
                               11  end;
                               12  /
                              
                              Type body created.
                              
                              SQL>
                              SQL> create type rooster  under chicken (
                                2  overriding member function make_a_noise return varchar2
                                3  )
                                4  ;
                                5  /
                              
                              Type created.
                              
                              SQL>
                              SQL> create or replace type body rooster as
                                2
                                3
                                4      overriding member function make_a_noise return varchar2
                                5      is
                                6      begin
                                7          return 'That''s a joke... I say, that''s a joke, son.';
                                8      end;
                                9
                               10
                               11  end;
                               12  /
                              
                              Type body created.
                              
                              SQL>
                              SQL>
                              SQL>
                              SQL>
                              SQL> declare
                                2      my_aviary birds_nt := birds_nt();
                                3  begin
                                4      my_aviary.extend(5);
                                5      my_aviary(1) := duck('Donald');
                                6      my_aviary(2) := chicken('Chicken Little');
                                7      my_aviary(3) := canary('Tweetie Pie');
                                8      my_aviary(4) := rooster('Foghorn Leghorn');
                                9      my_aviary(5) := bird('?');
                               10      for idx in my_aviary.first()..my_aviary.last()
                               11      loop
                               12          dbms_output.put_line( my_aviary(idx).fly() );
                               13          dbms_output.put_line( my_aviary(idx).make_a_noise() );
                               14      end loop;
                               15  end;
                               16  /
                              my name is Donald
                              quack!
                              my name is Chicken Little
                              cluck!
                              my name is Tweetie Pie
                              I taught I taw a puddy tat!!!
                              my name is Foghorn Leghorn
                              That's a joke... I say, that's a joke, son.
                              my name is ?
                              generic twitter
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL>
                              Now, in that loop woudl it make sense to execute a peck() on all of those entries? Nope. Furthermore as far as I can tell it is not possible to find out what sub-type an object is from with PL/SQL, so you couldn't even use Padders's TREAT ploy.

                              So: if you want to do something generic use a super-type. If you want to do something specific use a sub-type.

                              Cheers, APC

                              blog: http://radiofreetooting.blogspot.com
                              • 12. Re: plsql object type polymorphism
                                713310
                                Thanks for the example APC ... funny stuff. I see where you are coming from.

                                Maybe my code snippet is not a valid example of polymorphism, but it is certainly something that seems like it should compile, from a purely pedagogical point of view.
                                • 13. Re: plsql object type polymorphism
                                  Hoek
                                  Compilers don't 'think' from a 'purely pedagogical point of view'. That's a 'human thing', and on AI is still being worked.
                                  They tend to 'think' binary and rely on syntax and lots of complicated other things, we humans have defined for the compiler.
                                  only kidding here
                                  Maybe they're better off than us, tiny humans, with all our emotions and emotional bugs ;)

                                  On the other hand: perhaps you mean an 'academical' instead of a 'pedagogical' point of view...
                                  • 14. Re: plsql object type polymorphism
                                    713310
                                    Yes, academical point of view. Thank you.
                                    1 2 Previous Next