Oracle Objects, Types and Collections: Part 1

I could have called this OO In Oracle: A Techie’s View. But the Sorcerer of OO is a much cooler title. ;-) You have to pronounce OO as ooh! as in Ooh and Aah!

What is OO? OO stands for Object Oriented. OOD is OO Design, OOA is OO Analysis and OOP is Object Oriented Programming. Oracle is an ORDBMS, an Object Relational Database Management System. In this article, I am going to present a sample java class and show how you would implement the same class in Oracle. This is a technical article comparing Oracle objects with Java. In the next couple of articles, I will demonstrate inheritance, polymorphism and other traits expected of an OO language. I will also cover type evolution, an important aspect of types in Oracle.


PL/SQL, the procedural language for Oracle, is not particularly OO in the classical sense. It does provide quite a bit of OOness though. Via packages, you have encapsulation and abstraction. PL/SQL is not really intended to be OO but object methods are implemented using PL/SQL.

Oracle provides Object Types to implement the OO in ORDBMS. One of the strengths of an RDBMS is the abundance of generic data types. A type rich environment lets you express your data naturally. Object Types let you define more specific types using the existing types in the system.

Let’s create a class and a demo in Java and then we’ll create the equivalent class and demo in PL/SQL.

public class Calculator
{
double value;
public Calculator( double amt )
{
value = amt;
print();
}
public Calculator( )
{
value = 0;
print();
}
public void add( double amt )
{
value += amt;
print();
}
public void subtract( double amt )
{
value -= amt;
print();
}
public void print()
{
System.out.println( "Value is: " + value );
}
}

What this class does is define a Calculator type. The type has one attribute: value and three methods: add, subtract and print. There are also two constructors (initializing functions), one with a parameter and one with out.

Here is a test class to call this class:

class demo
{
public static void main(String args[])
{
Calculator calc = new Calculator(1);
Calculator calc1 = new Calculator();
calc.add(5);
calc1.add(10);
calc1.subtract(2);
}
}

It’s pretty easy to read. “class demo” is the name of our demo, ” public static void main(String args[])” tells Java that this will be a procedure to run rather than be called from another class. We next create a new calc variable and initialize its value to 1. We then create a variable called calc1 and let it value default to 0 (see the constructor in the class). Then we add 5 to variable calc, add 10 to variable calc1 and finally subtract 2 from calc1.

If you run this, you will get:

Value is 1.0
Value is 0.0
Value is 6.0
Value is 10.0
Value is 8.0

And now for the equivalent in Oracle:

In Oracle, creating a “class” is a two-step process. First, we have to declare the object type:

CREATE OR REPLACE TYPE Calculator AS OBJECT (
value NUMBER,
CONSTRUCTOR FUNCTION Calculator( value IN NUMBER )
RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION Calculator
RETURN SELF AS RESULT,
MEMBER PROCEDURE print,
MEMBER PROCEDURE add1( p_amt IN NUMBER ),
MEMBER PROCEDURE subtract( p_amt IN NUMBER )
)
NOT FINAL;
/

Here we are creating a type with one attribute: value and three methods: add1, subtract and print.

Note that add is apparently a reserved word so I added the 1 to add to get add1. Subtract is not a reserved word.

As in the java class, we created two constructors; one with a parameter and one without.

The NOT FINAL is a directive that will allow us to SUBTYPE this TYPE. This is required for a subtype to inherit the attributes and methods of a supertype.

Did you notice that the type is just declarative? That is what I meant that a type in Oracle is a two-step process. Here is the executable portion of our type:

CREATE OR REPLACE TYPE BODY Calculator AS
CONSTRUCTOR FUNCTION Calculator( value IN NUMBER )
RETURN SELF AS RESULT IS
BEGIN
SELF.value := value;
print;
RETURN;
END;
CONSTRUCTOR FUNCTION Calculator
RETURN SELF AS RESULT IS
BEGIN
SELF.value := 0;
print;
RETURN;
END;
MEMBER PROCEDURE print IS
BEGIN
DBMS_OUTPUT.PUT_LINE( TO_CHAR( value ) );
END;
MEMBER PROCEDURE add1( p_amt IN NUMBER ) IS
BEGIN
value := value + p_amt;
print;
END;
MEMBER PROCEDURE subtract( p_amt IN NUMBER ) IS
BEGIN
value := value - p_amt;
print;
END;
END;
/

Constructors initialize a type. Before you can assign variables to or access a type, you MUST initialize it. Most types will let you assign values to the type as part of the initialization. The first constructor, the one with the parameter is assigning a 1 to the attribute value.

By default, Oracle creates a default constructor for every type you create. A constructor is ALWAYS the same name as the type and the default constructor takes as parameters the list of attributes of that type. So, for the example above, Oracle automatically created a Calculator( value IN NUMBER ) constructor. I overrode that constructor so that I could make the call to print in that function. The constructor MUST have as parameters the exact list of attributes and the exact list of data types to override the default constructor. If I had called the parameter to my constructor, p_value instead of value, i.e. Calculator( p_value IN NUMBER ), I would have received a runtime error (PLS-00307) when accessing it.

Every constructor and method is automatically assigned a parameter called SELF. SELF refers to the INSTANCE of the object. In the constructor, notice that I referred to the attribute as SELF.value and the parameter just as value. When you see SELF in an object, it is referring to the current instance of that object.

And now for the demo:

DECLARE
calc Calculator;
calc1 Calculator;
BEGIN
calc := Calculator(1);
calc1 := Calculator();
calc.add1(5);
calc1.add1(10);
calc1.subtract(2);
END;
/

If you run this in sql*plus (you have to turn on serveroutput first), you will get:


Value is 1.0
Value is 0.0
Value is 6.0
Value is 10.0
Value is 8.0

And that does it. This is the basic format for creating a type in oracle. Like Java, it is very straightforward. I think it’s even more readable than Java but I’m biased.

Next up will be inheritance.

Let me know what you think,

Lewis

Technorati : , , , , , , , , , , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

1 Comment »