Object Types

Object types define how to store data and define API operations, also known as method functions or procedures. Object types are also known as classes in many OO programming languages. Object types mirror that of an PL/SQL package, an object body implements the object type just as a package body implements a package specification. The object type hides the operation details known as encapsulation and also masks the complexity known as abstraction. The current method for visually representing object types is generally done in UML (Unified Modeling Language).

OO programming has two types of API interfaces in object types

Static static methods allow you to access object type variables and methods without creating an instance of a class, static vaiables in PL/SQL are not available. you can implement static methods like package functions and procedures.
Instance Instance methods let you access object variables and methods of an instance of a class, they are only available after you have created an instance of an object type.

Oracle lets you create object types and bodies as SQL datatypes, you can use these as SQL datatypes in four situtions

Objects can be of two types

Persistant objects are stored in a database table and have a unique object identifier (standalone) or are embedded in another object (embedded)
Transient are not stored in the database and have a limited lifetime to the duration of their use in a PL/SQL block.

Object Basics

Objects share there namespace with other objects except for triggers, you must grant execute on the object to allow other to use it. Classes cannot have a return type, a class instantiation returns a copy or instance of a class.

Object types have a some specialized functions, however you can only implement one MAP or Order not both

constructor you can implement one or more but must follow the overriding rules, constructor functions return an instance of the object type, known in PL/SQL as self (not Java's this).
map it limits you to testing for equality based on a single number that identifies a class instance. It is more flexible and the order function because it can take parameters of any SQL datatype.
order allows to to pass an object instance into another object and compare whether they are equal.

To implement an object you can follow below

Prototype

create or replace object type <object_name>
  [authid {definer | current_user}] IS object
  (  [instance_variables {sql_datatype | plsql_datatype}],

     [CONSTRUCTOR function <constructor_name>
     [( parameter_list)] return result as self,

     [{member | static} function <function_name>
     [( parameter_list)] return {sql_datatype | plsql_datatye},

     [{member | static} procedure <procedure_name>
     [( parameter_list)],

     {[map function <map_name> return {char | date | number| varchar2} |
      [order function <order_name> return {sql_datatype | plsql_datatype}}])

     [not] instantiable [not] final;
/

Create the object type 
-- Object definition
CREATE OR REPLACE TYPE hello_there IS OBJECT
( who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

-- Object Body
CREATE OR REPLACE TYPE BODY hello_there IS
  
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    self := hello;
    RETURN;
  END hello_there;
  
  CONSTRUCTOR FUNCTION hello_there (who VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    RETURN;
  END hello_there;

  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN self.who;
  END get_who;

  MEMBER PROCEDURE set_who (who VARCHAR2) IS
  BEGIN
    self.who := who;
  END set_who;
  
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||self.who);
  END to_string;
  
END;
/
Using the Object DECLARE
   hello HELLO_THERE := hello_there;           -- you can also use hello_there()
BEGIN
   hello.to_string();
END;
/

Getters and Setters

As in other OO programming languages it is common to use getters and setters methods, that modify or get instance variables.

Example 
-- Object definition
CREATE OR REPLACE TYPE hello_there IS OBJECT
( who VARCHAR2(20)
, CONSTRUCTOR FUNCTION hello_there
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION hello_there
  ( who VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_who RETURN VARCHAR2
, MEMBER PROCEDURE set_who (who VARCHAR2)
, MEMBER PROCEDURE to_string )
INSTANTIABLE NOT FINAL;
/

-- Object Body
CREATE OR REPLACE TYPE BODY hello_there IS
  
  CONSTRUCTOR FUNCTION hello_there RETURN SELF AS RESULT IS
    hello HELLO_THERE := hello_there('Generic Object.');
  BEGIN
    self := hello;
    RETURN;
  END hello_there;
  
  CONSTRUCTOR FUNCTION hello_there (who VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    RETURN;
  END hello_there;

  MEMBER FUNCTION get_who RETURN VARCHAR2 IS
  BEGIN
    RETURN self.who;
  END get_who;

  MEMBER PROCEDURE set_who (who VARCHAR2) IS
  BEGIN
    self.who := who;
  END set_who;
  
  MEMBER PROCEDURE to_string IS
  BEGIN
    dbms_output.put_line('Hello '||self.who);
  END to_string;
  
END;
/

Static Member Methods

Static functions and procedures let you use an object type like a standard package. Static methods can create instances of their object type, but they are limited to working with instances of the object like external PL/SQL blocks.

Example 
-- Object type definition
CREATE OR REPLACE TYPE item_object IS OBJECT
( item_title    VARCHAR2(60),
  item_subtitle VARCHAR2(60),
  CONSTRUCTOR FUNCTION item_object RETURN SELF AS RESULT,
  -- overloaded constructor
  CONSTRUCTOR FUNCTION item_object (item_title VARCHAR2, item_subtitle VARCHAR2) RETURN SELF AS RESULT,
  STATIC FUNCTION get_item_object (item_id NUMBER) RETURN ITEM_OBJECT,
  MEMBER FUNCTION to_string RETURN VARCHAR2 
)
INSTANTIABLE NOT FINAL;
/

-- Object type body
CREATE OR REPLACE TYPE BODY item_object IS
  
  CONSTRUCTOR FUNCTION item_object RETURN SELF AS RESULT IS
    item ITEM_OBJECT := item_object('Generic Title','Generic Subtitle');
  BEGIN
    self := item;
    RETURN;
  END item_object;
  
  CONSTRUCTOR FUNCTION item_object
  (item_title VARCHAR2, item_subtitle VARCHAR2)
  RETURN SELF AS RESULT IS
  BEGIN
    self.item_title := item_title;
    self.item_subtitle := item_subtitle;
    RETURN;
  END item_object;

  STATIC FUNCTION get_item_object (item_id NUMBER) RETURN ITEM_OBJECT IS
    item ITEM_OBJECT;
    CURSOR c (item_id_in NUMBER) IS
      SELECT item_title, item_subtitle FROM item WHERE item_id = item_id_in;  
  BEGIN
    FOR i IN c (item_id) LOOP
      item := item_object(i.item_title,i.item_subtitle);     -- we create the object here and return it
    END LOOP;
    RETURN item;
  END get_item_object;

  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN '['||self.item_title||']['||self.item_subtitle||']';
  END to_string;
  
END;
/

-- Test whether the static constructor works.
BEGIN
  dbms_output.put_line(item_object.get_item_object(1050).to_string);
END;
/


Note just to break this down the item_object runs the constructor and creates the object, it passes back the 
ref to the object, using the ref you then run get_item_object passing id 1050, you get back the object 
ref which you now run the to_string method of that ref. it a bit like this

ref := item_object();
ref.get_item_object(1050);
ref.to_string();

but its all in one statement

Comparing Objects Values

In Java you use the equals method to compare objects, normally you override this method. In Oracle you have a master template that you implement through SQL DDL syntax, Oracle provides two predefined member functions - map and order. You can only implement one of these otherwise Oracle will throw an error. Subclasses cannot override either map or order.

MAP example 
-- Create the object definition
CREATE OR REPLACE TYPE map_comp IS OBJECT
( who VARCHAR2(30),
  CONSTRUCTOR FUNCTION map_comp (who VARCHAR2) RETURN SELF AS RESULT,
  MAP MEMBER FUNCTION equals RETURN VARCHAR2 
)
INSTANTIABLE NOT FINAL;
/

-- Create the object body
CREATE OR REPLACE TYPE BODY map_comp IS
  CONSTRUCTOR FUNCTION map_comp (who VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    RETURN;
  END map_comp;

  MAP MEMBER FUNCTION equals RETURN VARCHAR2 IS
  BEGIN
    RETURN self.who;
  END equals;
END;
/

-- Create the objects
DECLARE
  -- Declare a collection of an object type.
  TYPE object_list IS TABLE OF MAP_COMP;

  -- Initialize four objects in mixed alphabetical order.
  object1 MAP_COMP := map_comp('Ron Weasley');
  object2 MAP_COMP := map_comp('Harry Potter');
  object3 MAP_COMP := map_comp('Luna Lovegood');
  object4 MAP_COMP := map_comp('Hermione Granger');

  -- Define a collection of the object type.
  objects OBJECT_LIST := object_list(object1, object2, object3, object4);

  -- Swaps A and B.
  PROCEDURE swap (a IN OUT MAP_COMP, b IN OUT MAP_COMP) IS
    c MAP_COMP;
  BEGIN
    c := b;
    b := a;
    a := c;
  END swap;

BEGIN
  -- A bubble sort.
  FOR i IN 1..objects.COUNT 
  LOOP
    FOR j IN 1..objects.COUNT 
    LOOP
      IF objects(i).equals = LEAST(objects(i).equals,objects(j).equals) 
      THEN
        swap(objects(i),objects(j));
      END IF;
    END LOOP;
  END LOOP;

  -- Print reorderd objects.
  FOR i IN 1..objects.COUNT 
  LOOP
    dbms_output.put_line(objects(i).equals);
  END LOOP;
END;
/

-- Create a table to hold the objects
CREATE TABLE persistent_object
( persistent_object_id NUMBER
, mapping_object       MAP_COMP );

-- Create a sequence
CREATE SEQUENCE persistent_object_s1;

-- Insert instances of objects.
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Frodo Baggins'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Sam "Wise" Gamgee'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Meriadoc Brandybuck'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Perigrin Took'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Legolas son of Thranduil'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Aragorn son of Arathorn'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Boromir son of Denthor'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Gandolf the Gray'));
INSERT INTO persistent_object VALUES (persistent_object_s1.nextval,map_comp('Gimli the Drawf'));

-- Test the comparison 
COLUMN primary_key FORMAT 9999999 HEADING "Primary|Key ID"
COLUMN fellowship  FORMAT A30     HEADING "Fellowship Member"

SELECT   persistent_object_id AS primary_key
,        TREAT(mapping_object AS map_comp).equals() AS fellowship
FROM     persistent_object
WHERE    mapping_object IS OF (map_comp)
ORDER BY 2;
ORDER example 
-- Create object definition
CREATE OR REPLACE TYPE order_comp IS OBJECT
( who   VARCHAR2(20),
  movie VARCHAR2(20),
  CONSTRUCTOR FUNCTION order_comp (who VARCHAR2,movie VARCHAR2) RETURN SELF AS RESULT,
  MEMBER FUNCTION to_string RETURN VARCHAR2,
  ORDER MEMBER FUNCTION equals (object order_comp) RETURN NUMBER 
)
INSTANTIABLE NOT FINAL;
/

-- Create the object body
CREATE OR REPLACE TYPE BODY order_comp IS
  
  CONSTRUCTOR FUNCTION order_comp (who VARCHAR2, movie VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    self.movie := movie;
    RETURN;
  END order_comp;

  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN '['||self.movie||']['||self.who||']';
  END to_string;
  
  ORDER MEMBER FUNCTION equals (object order_comp) RETURN NUMBER IS
  BEGIN
    IF self.movie < object.movie 
    THEN
      RETURN 1;
    ELSIF self.movie = object.movie AND self.who < object.who 
    THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END equals;
  
END;
/

-- Create a table of objects
DECLARE
  -- Declare a collection of an object type.
  TYPE object_list IS TABLE OF ORDER_COMP;
  
  -- Initialize four objects in mixed alphabetical order.
  object1 ORDER_COMP := order_comp('Ron Weasley','Harry Potter 1');
  object2 ORDER_COMP := order_comp('Harry Potter','Harry Potter 1');
  object3 ORDER_COMP := order_comp('Luna Lovegood','Harry Potter 5');
  object4 ORDER_COMP := order_comp('Hermione Granger','Harry Potter 1');
  object5 ORDER_COMP := order_comp('Hermione Granger','Harry Potter 2');
  object6 ORDER_COMP := order_comp('Harry Potter','Harry Potter 5');
  object7 ORDER_COMP := order_comp('Cedric Diggory','Harry Potter 4');
  object8 ORDER_COMP := order_comp('Severus Snape','Harry Potter 1');

  -- Define a collection of the object type.
  objects OBJECT_LIST := object_list(object1, object2, object3, object4,object5, object6, object7, object8);

  -- Swaps A and B.
  PROCEDURE swap (a IN OUT ORDER_COMP, b IN OUT ORDER_COMP) IS
    c ORDER_COMP;
  BEGIN
    c := b;
    b := a;
    a := c;
  END swap;
  
BEGIN
  -- A bubble sort.
  FOR i IN 1..objects.COUNT 
  LOOP
    FOR j IN 1..objects.COUNT 
    LOOP
      IF objects(i).equals(objects(j)) = 0 
      THEN
        swap(objects(i),objects(j));
      END IF;
    END LOOP;
  END LOOP;
  
  -- Print reorderd objects.
  FOR i IN 1..objects.COUNT 
  LOOP
    dbms_output.put_line(objects(i).to_string);
  END LOOP;
END;
/

Inheritance and Polymorphim

Objects are extensible because you can add to their capabilities by building subclasses, subclasses inherit the behaviors of other classes, which become known as superclasses. Subclasses can also override functions and procedures of the superclass, this whole processes is know as morphing. Polymorphing is the process of multiple subclasses inheriting the behaviors of superclasses. Oracle only support single inheritance but you can overcome this limitation.

Orcale uses the keyword under (same as extends in Java) to subclass a superclass. You must state that you intend to override a method by using the keyword overriding. You cannot override the superclass variables the compiler will complain.

  
-- Create the object definition (superclass object)
CREATE OR REPLACE TYPE order_comp IS OBJECT
( who   VARCHAR2(20),
  movie VARCHAR2(20),
  CONSTRUCTOR FUNCTION order_comp (who VARCHAR2,movie VARCHAR2) RETURN SELF AS RESULT,
  MEMBER FUNCTION to_string RETURN VARCHAR2,
  ORDER MEMBER FUNCTION equals (object order_comp) RETURN NUMBER 
)
INSTANTIABLE NOT FINAL;
/

-- Create the object body (superclass object)
CREATE OR REPLACE TYPE BODY order_comp IS
  
  CONSTRUCTOR FUNCTION order_comp
  (who VARCHAR2, movie VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    self.movie := movie;
    RETURN;
  END order_comp;

  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN '['||self.movie||']['||self.who||']';
  END to_string;
  
  ORDER MEMBER FUNCTION equals (object order_comp) RETURN NUMBER IS
  BEGIN
    IF self.movie < object.movie THEN
      RETURN 1;
    ELSIF self.movie = object.movie AND self.who < object.who THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END equals;
  
END;
/

-- Create the object definition (subclass)
CREATE OR REPLACE TYPE order_subcomp UNDER order_comp
( subtitle  VARCHAR2(20),
  CONSTRUCTOR FUNCTION order_subcomp (who VARCHAR2, movie VARCHAR2, subtitle VARCHAR2) RETURN SELF AS RESULT,
  OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2     -- the overriding keyword
)
INSTANTIABLE FINAL;
/

-- Create the object body (subclass)
CREATE OR REPLACE TYPE BODY order_subcomp IS
  
  CONSTRUCTOR FUNCTION order_subcomp
  (who VARCHAR2, movie VARCHAR2, subtitle VARCHAR2)
  RETURN SELF AS RESULT IS
  BEGIN
    self.who := who;
    self.movie := movie;
    self.subtitle := subtitle;
    RETURN;
  END order_subcomp;

  OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN (self as order_comp).to_string||'['||self.subtitle||']';
  END to_string;
   
END;
/

-- Create a table of objects using the subclass
DECLARE
  -- Declare a collection of an object type.
  TYPE object_list IS TABLE OF ORDER_COMP;
  
  -- Initialize one subtype.
  object1 ORDER_SUBCOMP := order_subcomp('Ron Weasley','Harry Potter 1','Socerer''s Stone');
  -- Initialize seven types.
  object2 ORDER_COMP := order_comp('Harry Potter','Harry Potter 1');
  object3 ORDER_COMP := order_comp('Luna Lovegood','Harry Potter 5');
  object4 ORDER_COMP := order_comp('Hermione Granger','Harry Potter 1');
  object5 ORDER_COMP := order_comp('Hermione Granger','Harry Potter 2');
  object6 ORDER_COMP := order_comp('Harry Potter','Harry Potter 5');
  object7 ORDER_COMP := order_comp('Cedric Diggory','Harry Potter 4');
  object8 ORDER_COMP := order_comp('Severus Snape','Harry Potter 1');

  -- Define a collection of the object type.
  objects OBJECT_LIST := object_list(object1, object2, object3, object4,object5, object6, object7, object8);

  -- Swaps A and B.
  PROCEDURE swap (a IN OUT ORDER_COMP, b IN OUT ORDER_COMP) IS
    c ORDER_COMP;
  BEGIN
    c := b;
    b := a;
    a := c;
  END swap;
  
BEGIN
  -- A bubble sort.
  FOR i IN 1..objects.COUNT 
  LOOP
    FOR j IN 1..objects.COUNT 
    LOOP
      IF objects(i).equals(objects(j)) = 1 
      THEN
        swap(objects(i),objects(j));
      END IF;
    END LOOP;
  END LOOP;
  -- Print reorderd objects.
  FOR i IN 1..objects.COUNT 
  LOOP
    dbms_output.put_line(objects(i).to_string);
  END LOOP;
END;
/