Introduction to Object Type Methods

View All Scripts Login to Run Script

Script Name Introduction to Object Type Methods
Description This script explores member methods and the SELF value, static methods, non-instantiable methods and how invoke a supertype's method explicitly. This code is fully explained in the accompanying blog post at https://stevenfeuersteinonplsql.blogspot.com/2019/09/plsql-101-static-and-member-methods-of.html
Area PL/SQL General
Contributor Steven Feuerstein
Created Monday September 09, 2019

Statement 1

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   MEMBER FUNCTION to_string RETURN VARCHAR2 
) 
NOT FINAL;

Type created.

Statement 2

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   MEMBER FUNCTION to_string RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN    'FOOD! ' 
             || SELF.NAME 
             || ' - ' 
             || SELF.food_group 
             || ' - ' 
             || SELF.grown_in; 
   END; 
END;

Type created.

Statement 3

DECLARE 
   squirrels_love_them  food_ot :=  
      food_ot ('Acorn', 'Protein', 'Tree'); 
BEGIN 
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string()); 
END;

Statement processed.
FOOD! Acorn - Protein - Tree

Statement 4

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   MEMBER FUNCTION to_string (SELF IN food_ot) RETURN VARCHAR2 
) 
NOT FINAL;

Type created.

Statement 5

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   MEMBER FUNCTION to_string (SELF IN food_ot) RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN    'FOOD! ' 
             || SELF.NAME 
             || ' - ' 
             || SELF.food_group 
             || ' - ' 
             || SELF.grown_in; 
   END; 
END;

Type created.

Statement 6

DECLARE 
   squirrels_love_them  food_ot :=  
      food_ot ('Acorn', 'Protein', 'Tree'); 
BEGIN 
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string()); 
END;

Statement processed.
FOOD! Acorn - Protein - Tree

Statement 7

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   MEMBER FUNCTION to_string (SELF IN OUT food_ot) RETURN VARCHAR2 
) 
NOT FINAL;

Type created.

Statement 8

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   MEMBER FUNCTION to_string (SELF IN OUT food_ot) RETURN VARCHAR2 
   IS 
   BEGIN 
      /* Enforce upper case for all values */ 
      SELF.name := UPPER (SELF.name); 
      SELF.food_group := UPPER (SELF.food_group); 
      SELF.grown_in := UPPER (SELF.grown_in); 
       
      RETURN    'FOOD! ' 
             || SELF.NAME 
             || ' - ' 
             || SELF.food_group 
             || ' - ' 
             || SELF.grown_in; 
   END; 
END;

Type created.

Statement 9

DECLARE 
   squirrels_love_them  food_ot :=  
      food_ot ('Acorn', 'Protein', 'Tree'); 
BEGIN 
   DBMS_OUTPUT.put_line (squirrels_love_them.to_string()); 
   DBMS_OUTPUT.put_line ('Still upper case? ' || squirrels_love_them.name); 
END;

Statement processed.
FOOD! ACORN - PROTEIN - TREE
Still upper case? ACORN

Statement 10

CREATE OR REPLACE TYPE food_ot AS OBJECT 
( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   STATIC FUNCTION version RETURN VARCHAR2 
) 
   NOT FINAL;

Type created.

Statement 11

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   STATIC FUNCTION version RETURN VARCHAR2 
   IS 
   BEGIN 
      /*  
      Version history 
      2018-09-14 1.0.1 Type deployed to production 
      2019-03-22 1.0.2 Added grown_in attribute       
      */ 
      RETURN '1.0.2'; 
   END; 
END;

Type created.

Statement 12

BEGIN 
   DBMS_OUTPUT.put_line (food_ot.version); 
END;

Statement processed.
1.0.2

Statement 13

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
    contains_chocolate CHAR (1) 
  , year_created NUMBER (4)  
);

Type created.

Statement 14

BEGIN 
   DBMS_OUTPUT.put_line (dessert_ot.version); 
END;

Statement processed.
1.0.2

Statement 15

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
    contains_chocolate CHAR (1) 
  , year_created NUMBER (4)  
  , OVERRIDING STATIC FUNCTION version RETURN VARCHAR2 
);

Errors: TYPE DESSERT_OT
Line/Col: 4/32 PLS-00169: modifier 'STATIC' conflicts with prior 'OVERRIDING' specification

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 16

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
    contains_chocolate CHAR (1) 
  , year_created NUMBER (4)  
  , STATIC FUNCTION version RETURN VARCHAR2 
);

Type created.

Statement 17

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   STATIC FUNCTION version RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN 'v10.4.5'; 
   END; 
END;

Type created.

Statement 18

BEGIN 
   DBMS_OUTPUT.put_line (dessert_ot.version); 
END;

Statement processed.
v10.4.5

Statement 19

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
    contains_chocolate CHAR (1) 
  , year_created NUMBER (4)  
  , STATIC PROCEDURE version  
);

Type created.

Statement 20

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   STATIC PROCEDURE version  
   IS 
   BEGIN 
      DBMS_OUTPUT.PUT_LINE ('v10.4.5'); 
   END; 
END;

Type created.

Statement 21

BEGIN 
   DBMS_OUTPUT.put_line (dessert_ot.version); 
   dessert_ot.version; 
END;

Statement processed.
1.0.2
v10.4.5

Statement 22

DROP TYPE dessert_ot FORCE

Type dropped.

Statement 23
```
DROP TYPE dessert_ot FORCE
/
```

Statement 24

You cannot use the constructor of a non-instantiable type, but you can use the constructor of a subtype!

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   NOT INSTANTIABLE MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL NOT INSTANTIABLE; 
/

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
   contains_chocolate   CHAR (1)
 , OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
   IS 
   BEGIN 
      RETURN 1; 
   END; 
END; 
/

DECLARE 
   l_food food_ot ; 
BEGIN 
   l_food := dessert_ot ('Apple', 'N'); 
   DBMS_OUTPUT.PUT_LINE (l_food.name); 
END; 
/

Statement 25

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   -- Generic foods cannot have a price, but we can 
   -- insist that all subtypes DO implement a price 
   -- function. 
   NOT INSTANTIABLE MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL NOT INSTANTIABLE;

Type created.

Statement 26

DECLARE 
   l_food food_ot := food_ot ('a', 'b', 'c'); 
BEGIN 
   DBMS_OUTPUT.PUT_LINE (l_food.name); 
END;

ORA-06550: line 2, column 22:
PLS-00713: attempting to instantiate a type that is NOT INSTANTIABLE

More Details: https://docs.oracle.com/error-help/db/ora-06550

Statement 27

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
   contains_chocolate   CHAR (1) 
 , year_created         NUMBER (4) 
 , OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
) 
NOT FINAL;

Type created.

Statement 28

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   OVERRIDING MEMBER FUNCTION price 
      RETURN NUMBER 
   IS 
      multiplier   NUMBER := 1; 
   BEGIN 
      DBMS_OUTPUT.put_line ('Dessert price!'); 
 
      IF SELF.contains_chocolate = 'Y' 
      THEN 
         multiplier := 2; 
      END IF; 
 
      IF SELF.year_created < 1900 
      THEN 
         multiplier := multiplier + 0.5; 
      END IF; 
 
      RETURN (10.00 * multiplier); 
   END; 
END;

Type created.

Statement 29

DECLARE 
   l_apple dessert_ot := dessert_ot ('Apple', 'Fruit', 'Tree', 'N', -5000); 
BEGIN 
   DBMS_OUTPUT.PUT_LINE (l_apple.name); 
END;

Statement processed.
Apple

Statement 30

DROP type dessert_ot force

Type dropped.

Statement 31

CREATE OR REPLACE TYPE food_ot AS OBJECT ( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   MEMBER FUNCTION to_string 
      RETURN VARCHAR2 
) 
NOT FINAL;

Type created.

Statement 32

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   MEMBER FUNCTION to_string 
      RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN    'FOOD! ' 
             || SELF.NAME 
             || ' - ' 
             || SELF.food_group 
             || ' - ' 
             || SELF.grown_in; 
   END; 
END;

Type created.

Statement 33

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot ( 
    contains_chocolate CHAR (1) 
  , year_created NUMBER (4)  
  , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 
);

Type created.

Statement 34

CREATE OR REPLACE TYPE BODY dessert_ot 
IS 
   OVERRIDING MEMBER FUNCTION to_string  RETURN VARCHAR2 
   IS 
   BEGIN 
      /* Add the supertype (food) string to the subtype string.... */ 
      RETURN    'DESSERT! With Chocolate? ' 
             || contains_chocolate 
             || ' created in ' 
             || SELF.year_created 
             || chr(10) 
             || '...which is a...' 
             || (SELF as food_ot).to_string; 
   END; 
END;

Type created.

Statement 35

DECLARE 
   TYPE foodstuffs_nt IS TABLE OF food_ot; 
 
   fridge_contents foodstuffs_nt 
         := foodstuffs_nt ( 
               food_ot ('Eggs benedict', 'PROTEIN', 'Farm') 
             , dessert_ot ('Strawberries and cream' 
                        , 'FRUIT', 'Backyard', 'N', 2001) 
            ); 
BEGIN 
   FOR indx in 1 .. fridge_contents.COUNT 
   LOOP 
      DBMS_OUTPUT.put_line (RPAD ('=', 60, '=')); 
      DBMS_OUTPUT.put_line (fridge_contents (indx).to_string); 
   END LOOP; 
END;

Statement processed.
============================================================
FOOD! Eggs benedict - PROTEIN - Farm
============================================================
DESSERT! With Chocolate? N created in 2001
...which is a...FOOD! Strawberries and cream - FRUIT - Backyard

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database