Introduction to Object Types, Part 1

View All Scripts Login to Run Script

Script Name Introduction to Object Types, Part 1
Description The code that accompanies my blog post, "PL/SQL 101: Introduction to Object Types, a.k.a, Classes". Which you can read at: https://stevenfeuersteinonplsql.blogspot.com/2019/08/plsql-101-introduction-to-object-types.html
Area SQL Types
Contributor Steven Feuerstein
Created Wednesday August 21, 2019

Statement 1

CREATE TYPE food_ot AS OBJECT 
( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100) 
) 
   NOT FINAL;

Type created.

Statement 2

DECLARE 
   TYPE food_rt IS RECORD 
   ( 
      name         VARCHAR2 (100), 
      food_group   VARCHAR2 (50), 
      grown_in     VARCHAR2 (100) 
   ); 
 
   my_favorite_vegetable   food_rt; 
BEGIN 
   my_favorite_vegetable.name := 'Brussels Sprouts'; 
   my_favorite_vegetable.food_group := 'Vegetables'; 
   my_favorite_vegetable.grown_in := 'Dirt'; 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

Statement processed.
Brussels Sprouts are yummy!

Statement 3

DECLARE 
   my_favorite_vegetable   food_ot; 
BEGIN 
   my_favorite_vegetable.name := 'Brussels Sprouts'; 
   my_favorite_vegetable.food_group := 'Vegetables'; 
   my_favorite_vegetable.grown_in := 'Dirt'; 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

ORA-06530: Reference to uninitialized composite ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SQL", line 1721

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

Statement 4

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

Statement processed.
Brussels Sprouts are yummy!

Statement 5

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

Type created.

Statement 6

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   CONSTRUCTOR FUNCTION food_ot 
      RETURN SELF AS RESULT 
   IS 
   BEGIN 
      RETURN; 
   END; 
 
   CONSTRUCTOR FUNCTION food_ot (NAME_IN IN VARCHAR2) 
      RETURN SELF AS RESULT 
   IS 
   BEGIN 
      self.name := NAME_IN; 
      RETURN; 
   END; 
END;

Type created.

Statement 7

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot ('Brussels Sprouts'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

Statement processed.
Brussels Sprouts are yummy!

Statement 8

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot (); 
BEGIN 
   my_favorite_vegetable.name := 'Brussels Sprouts'; 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

Statement processed.
Brussels Sprouts are yummy!

Statement 9

You can still use the default constructor.

DECLARE 
   my_favorite_vegetable food_ot  
      := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
      my_favorite_vegetable.name || ' are yummy!'); 
END;

Statement processed.
Brussels Sprouts are yummy!

Statement 10

What if we define a constructor whose parameter list matches the attribute list? Let's explore. The main reason you might want to do this is to ensure that special initialization logic occurs whenever an instance is initialized.

Can we override the default constructor?

CREATE OR REPLACE TYPE food_ot 
   AS OBJECT 
( 
   name VARCHAR2 (100), 
   food_group VARCHAR2 (50), 
   grown_in VARCHAR2 (100), 
   CONSTRUCTOR FUNCTION food_ot (name_in         IN VARCHAR2, 
                                 food_group_in   IN VARCHAR2, 
                                 grown_in_in     IN VARCHAR2) 
      RETURN SELF AS RESULT 
) 
   NOT FINAL;

Type created.

Statement 11

Now I have a constructor with three string arguments, just like the type has three string attributes.

Compiles just fine

CREATE OR REPLACE TYPE BODY food_ot  
IS  
   CONSTRUCTOR FUNCTION food_ot (name_in         IN VARCHAR2,  
                                 food_group_in   IN VARCHAR2,  
                                 grown_in_in     IN VARCHAR2)  
      RETURN SELF AS RESULT  
   IS  
   BEGIN  
      self.name := name_in;  
      self.food_group := grown_in_in;  
      self.grown_in := food_group_in;  
      RETURN;  
   END;  
END;

Type created.

Statement 12

OK, that didn't work. The PL/SQL engine could not distinguish between the automatically defined constructor and mine.

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END;

ORA-06550: line 3, column 10:
PLS-00307: too many declarations of 'FOOD_OT' match this call

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

Statement 13

But if I use named notation, since my parameter names are different from the attribute names, then it works. Notice the "wrong" grown-in value.

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot (name_in         => 'Brussels Sprouts', 
                  food_group_in   => 'Vegetables', 
                  grown_in_in     => 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END;

Statement processed.
Brussels Sprouts are grown in Vegetables

Statement 14

If I use named notation with the names of attributes, then the default constructor is called (notice the grown-in value).

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot (name         => 'Brussels Sprouts', 
                  food_group   => 'Vegetables', 
                  grown_in     => 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END;

Statement processed.
Brussels Sprouts are grown in Dirt

Statement 15

Which begs the question: what if I define my own constructor and use attribute names for parameter names?

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

Type created.

Statement 16

CREATE OR REPLACE TYPE BODY food_ot 
IS 
   CONSTRUCTOR FUNCTION food_ot (name         IN VARCHAR2, 
                                 food_group   IN VARCHAR2, 
                                 grown_in     IN VARCHAR2) 
      RETURN SELF AS RESULT 
   IS 
   BEGIN 
      self.name := name; 
      self.food_group := grown_in; 
      self.grown_in := food_group; 
      RETURN; 
   END; 
END;

Type created.

Statement 17

Check out the grown-in value: my constructor and not the default was used!

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot (name         => 'Brussels Sprouts', 
                  food_group   => 'Vegetables', 
                  grown_in     => 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END;

Statement processed.
Brussels Sprouts are grown in Vegetables

Statement 18

And now for the culmination of my exploration: even without named notation, my user-defined constructor completely overrides the default! Which is really helpful if you need to run some special logic any and every time an instance of a type is initialized.

DECLARE 
   my_favorite_vegetable   food_ot 
      := food_ot ('Brussels Sprouts', 'Vegetables', 'Dirt'); 
BEGIN 
   DBMS_OUTPUT.put_line ( 
         my_favorite_vegetable.name 
      || ' are grown in ' 
      || my_favorite_vegetable.grown_in); 
END;

Statement processed.
Brussels Sprouts are grown in Vegetables

Additional Information

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