Object oriented ROWTYPE

Introduction


Oracle offers an extra ODBMS for traditional OO PL/SQL programming. It is based on Oracle object type definitions, that are used to create tabels for. Access to such objects is made through an OROWID, which also care for normalization. To gain access to its values, you need to call the DEREF function. After these steps, the data is finally accessible to be handled with simple DML sataements.

In contrast to the OO-PL/SQL programming for the Oracle ODBMS, our design of Object Types is based on the common Oracle RDBMS, allowing for OO-access of the table data in the relational data model. We can even use the Type’s procedures just like an object’s methods. The generated object types can be compared to Oracle’s %ROWTYPE with additional methods acting as constructor or providing the common DML-instructions like SELECT, INSERT, UPDATE, DELET or MERGE. This makes manipulating data just as easy as calling methods on an object.

We can even provide constructors for primary key and unique key of a RDBMS table’s id making it very easy to get access to a specific row of that tabel. We can even have a nice TO_STRING method, giving us a text representation of that opbject for free, using DBMS_OUTPUT to display that information.

With the functionality introduced above, we can access any standard relational database model in a RDBMS. By introducing the virtual OO-model (OO-Layer) we can access any table column and use the whole range of DML-commands to work on that data.

The OO-ROWTYPES enable us to create more complex Business Objects (BO) describing our real world busines environment. We can create such BO’s by deriving from and/or composing several OO-ROWTYPES to encapsulate access to the data needed for that specific business object.

Implementation

1. Introducing the Object Type TYPE_OBJECT

Oracle does not supply a root object for an object hierarchy as i.e. Java does. Thus we introduce a root object ourselves, naming it TYPE_OBJECT:

CREATE OR REPLACE TYPE SCOTT.TYPE_OBJECT AS OBJECT(
-- attributes
object_type_name VARCHAR2(100)
-- member functions and procedures
, MEMBER PROCEDURE DBMS_OUTPUT
, MEMBER FUNCTION TO_STRING RETURN VARCHAR2
, MEMBER FUNCTION TO_CLOB RETURN CLOB
, MEMBER FUNCTION COMPARE(in_type1 TYPE_OBJECT, in_type2 TYPE_OBJECT) RETURN INTEGER
, ORDER MEMBER FUNCTION COMPARE2(in_other TYPE_OBJECT) RETURN INTEGER
) NOT FINAL NOT INSTANTIABLE

Now we have several advantages:

• We have a standard object interface providing such methods as TO_STRING and handling DBMS_OUTPUT in a common way


• Easy passing of derived Object Types as parameters in abritraty procedures or functions (object hierarchy)


• We can define an ORDER relation by overloading a COMPARE method, enabling us to compare any two objects

2. Introducing the OO-ROWTYPE

The SCOTT schema tables EMP and DEPT can be converted to an OO-ROWTYPE for our virtual ODBMS as follows:

CREATE OR REPLACE TYPE SCOTT.ROW_DEPT UNDER SCOTT.TYPE_OBJECT(
-- attributes
deptno NUMBER(2)
, dname VARCHAR2(14)
, loc VARCHAR2(13)
-- define constructors
, CONSTRUCTOR FUNCTION ROW_DEPT RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_DEPT( in_deptno NUMBER, in_dname VARCHAR2
, in_loc VARCHAR2) RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_DEPT(in_deptno NUMBER) RETURN SELF AS RESULT
-- member functions
, MEMBER FUNCTION ROW_EXISTS(in_deptno NUMBER) RETURN BOOLEAN
, OVERRIDING MEMBER FUNCTION COMPARE( in_type1 GLOBAL.TYPE_OBJECT
, in_type2 GLOBAL.TYPE_OBJECT
) RETURN INTEGER
-- member procedures
, MEMBER PROCEDURE ROW_INSERT
, MEMBER PROCEDURE ROW_UPDATE
, MEMBER PROCEDURE ROW_MERGE
, MEMBER PROCEDURE ROW_SAVE
, MEMBER PROCEDURE ROW_DELETE
, MEMBER PROCEDURE ROW_SELECT(in_deptno NUMBER)
, MEMBER PROCEDURE ROW_LOCK
, MEMBER PROCEDURE ROW_LOCK(in_deptno NUMBER)
, MEMBER PROCEDURE ROW_DEFAULT
) NOT FINAL

CREATE OR REPLACE TYPE SCOTT.ROW_EMP UNDER SCOTT.TYPE_OBJECT(
-- attributes
empno NUMBER(4)
, ename VARCHAR2(10)
, job VARCHAR2(9)
, mgr NUMBER(4)
, hiredate DATE
, sal NUMBER(7,2)
, comm NUMBER(7,2)
, deptno NUMBER(2)
-- constructors
, CONSTRUCTOR FUNCTION ROW_EMP RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_EMP( in_empno NUMBER, in_ename VARCHAR2
, in_job VARCHAR2, in_mgr NUMBER
, in_hiredate DATE, in_sal NUMBER
, in_comm NUMBER, in_deptno NUMBER
) RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_EMP(in_empno NUMBER) RETURN SELF AS RESULT
-- member functions
, MEMBER FUNCTION ROW_EXISTS(in_empno NUMBER) RETURN BOOLEAN
, OVERRIDING MEMBER FUNCTION compare(in_type1 GLOBAL.TYPE_OBJECT, in_type2 GLOBAL.TYPE_OBJECT
) RETURN INTEGER
-- member procedures
, MEMBER PROCEDURE ROW_INSERT
, MEMBER PROCEDURE ROW_UPDATE
, MEMBER PROCEDURE ROW_MERGE
, MEMBER PROCEDURE ROW_SAVE
, MEMBER PROCEDURE ROW_DELETE
, MEMBER PROCEDURE ROW_SELECT(in_empno NUMBER)
, MEMBER PROCEDURE ROW_LOCK
, MEMBER PROCEDURE ROW_LOCK(in_empno NUMBER)
, MEMBER PROCEDURE ROW_DEFAULT
) NOT FINAL

we can create the needed Container Types and Packages just as well :

CREATE OR REPLACE TYPE TABLE_EMP AS TABLE OF SCOTT.ROW_EMP;
CREATE OR REPLACE TYPE TABLE_DEPT AS TABLE OF SCOTT.ROW_DEPT;

CREATE OR REPLACE PACKAGE PA_DEPT IS
FUNCTION FU_SELECT RETURN TABLE_DEPT;
END;

CREATE OR REPLACE PACKAGE PA_EMP IS
FUNCTION FU_SELECT RETURN TABLE_EMP;
FUNCTION FS_DEPTNO(IN_DEPTNO IN EMP.DEPTNO%TYPE) RETURN TABLE_EMP;
FUNCTION FS_MGR(IN_MGR IN EMP.MGR%TYPE) RETURN TABLE_EMP;
END;

You can see the similarity between den %ROWTYPE and our generated OO-ROWTYPES. But OO_ROWTYPES provide more functionality:

• Constructors for primary- and unique keys


• Functions: ROW_EXIST checks if the given primary or unique key refrence already exists in the table of the RDBMS; COMPARE to be able to compare objects


• Procedures:
  All standrad DML commands, like ROW_INSERT, ROW_UPDATE, ROW_DELETE, ROW_MERGE and ROW_SELECT; ROW_DEFAULT to preset an object with the table’s default column values; ROW_SAVE replacing the MERGE instruction and adding a RETURNING clause to it, in contrast to Oracle’s MERGE; ROW_LOCK for pessimistic locking table row.

We can equip each OO_ROWTYPE with a set of suitable data-cartridges for the foreign keys and for the whole table and even create the appropriate. Data-cartridges are functions, returning a container table. Unfortunately, Oracle does not allow to have container types within a type. Thus we have to supply these data cartridges in a separate package.

Creating OO-ROWTYPEs is tedious, error-prone and – once the basic functionality is understood – monotonous and boring to code by hand. Why not let the computer do the work and have the OO-ROWTYPEs generated by a small program, that can read the DBMS’s metadata and knows how to write OO-ROWTYPE-Descriptions to a file. We can intergate the genertor into the DBMS by calling it via a small wrapper script.

3. Structure of a Business Object (BO-TYPEs)

We are now able to build largeer scale Business Objects by deriving from and / or composing several of the now available OO-ROWTYPEs.

We will demonstrate this by introducing a MANAGERs, DEPARTMENTs and ENTERPRISEs to the SCOTT schema:

• TYPE_MANAGER – is derived from ROW_EMP and contains references to all employees he she is responsible for:
  
  CREATE OR REPLACE TYPE TYPE_MANAGER UNDER ROW_EMP(
  -- attributes
  EMPLOYEES TABLE_EMP
  -- constructors
  , CONSTRUCTOR FUNCTION TYPE_MANAGER RETURN SELF AS RESULT
  , CONSTRUCTOR FUNCTION TYPE_MANAGER(IN_EMPNO NUMBER) RETURN SELF AS RESULT
  ) NOT FINAL
  


• TYPE_DEPARTMENT – is derived from ROW_DEPT and references all employees working in that department, including the newly installed manager. (Note here, that even managers are employees being derived from employees):
  
  CREATE OR REPLACE TYPE TYPE_DEPARTMENT UNDER ROW_DEPT(
  -- attributes
  EMPLOYEES TABLE_EMP
  -- constructors
  , CONSTRUCTOR FUNCTION TYPE_DEPARTMENT RETURN SELF AS RESULT
  , CONSTRUCTOR FUNCTION TYPE_DEPARTMENT(in_deptno NUMBER) RETURN SELF AS RESULT
  
  -- member functions
  , MEMBER FUNCTION GET_MANAGER RETURN TYPE_MANAGER
  ) NOT FINAL
  


• TYPE_ENTERPRISE – is a top noth object, being derived only from TYPE_OBJECTand supplying a company name, a president (shure guess to be a manager, thus being an employee, too), a list of departments and finally a bunch of employees (including managers again):
  
  CREATE OR REPLACE TYPE TYPE_ENTERPRISE UNDER TYPE_OBJECT(
  -- attributes
  NAME VARCHAR2(100)
  , PRESIDENT TYPE_MANAGER
  , DEPARTMENTS TABLE_DEPT
  , EMPLOYEES TABLE_EMP
  -- constructors
  , CONSTRUCTOR FUNCTION TYPE_ENTERPRISE RETURN SELF AS RESULT
  ) NOT FINAL

We can now call the generated object types and container using simple SELECT statements:

SQL> SELECT TYPE_DEPARTMENT(20) FROM DUAL;
TYPE_ENTERPRISE()(OBJECT_TYPE_NAME, DEPTNO, DNAME, LOC, NAME, PRESIDENT(OBJECT_T
--------------------------------------------------------------------------------
TYPE_DEPARTMENT('TYPE_DEPARTMENT', 20, 'RESEARCH', 'DALLAS',
TABLE_EMP(
ROW_EMP('ROW_EMP', 7369, 'SMITH', 'CLERK', 7902, '17.12.80', 800, NULL, 20),
ROW_EMP('ROW_EMP', 7566, 'JONES', 'MANAGER', 7839, '02.04.81', 2975, NULL, 20),
ROW_EMP('ROW_EMP', 7788, 'SCOTT', 'ANALYST', 7566, '19.04.87', 3000, NULL, 20),
ROW_EMP('ROW_EMP', 7876, 'ADAMS', 'CLERK', 7788, '23.05.87', 1100, NULL, 20),
ROW_EMP('ROW_EMP', 7902, 'FORD', 'ANALYST', 7566, '03.12.81', 3000, NULL, 20)
)
)


SQL> SELECT VALUE(e) FROM TABLE (TYPE_MANAGER(7698).EMPLOYEES) e;
VALUE(E)(OBJECT_TYPE_NAME, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
--------------------------------------------------------------------------------
ROW_EMP('ROW_EMP', 7499, 'ALLEN', 'SALESMAN', 7698, '20.02.81', 1600, 300, 30)
ROW_EMP('ROW_EMP', 7521, 'WARD', 'SALESMAN', 7698, '22.02.81', 1250, 500, 30)
ROW_EMP('ROW_EMP', 7654, 'MARTIN', 'SALESMAN', 7698, '28.09.81', 1250, 1400, 30)
ROW_EMP('ROW_EMP', 7844, 'TURNER', 'SALESMAN', 7698, '08.09.81', 1500, 0, 30)
ROW_EMP('ROW_EMP', 7900, 'JAMES', 'CLERK', 7698, '03.12.81', 950, NULL, 30)

We can even use a PL/SQL-Script:

DECLARE
e TYPE_ENTERPRISE := TYPE_ENTERPRISE();
BEGIN
e.dbms_output;
END;

That produces some nice DBMS Output:


SCOTT.TYPE_ENTERPRISE
(
OBJECT_TYPE_NAME = TYPE_ENTERPRISE
NAME = King Corporation
PRESIDENT = SCOTT.TYPE_MANAGER
(
OBJECT_TYPE_NAME = TYPE_MANAGER
EMPNO = 7839
ENAME = KING
JOB = PRESIDENT
MGR =
HIREDATE = 17.11.1981 00:00:00
SAL = 5000
COMM =
DEPTNO = 10
EMPLOYEES = <>
)
DEPARTMENTS = <>
EMPLOYEES = <>
)


Coding it in PL/SQL would look very similar:

DECLARE
m TYPE_MANAGER;
BEGIN
m := TYPE_MANAGER(7839);
m.sal := m.sal * 2;
m.row_update;
m.dbms_output;
END;

DBMS Output displayed:

SCOTT.TYPE_MANAGER
(
OBJECT_TYPE_NAME = TYPE_MANAGER
EMPNO = 7839
ENAME = KING
JOB = PRESIDENT
MGR =
HIREDATE = 17.11.1981 00:00:00
SAL = 10000
COMM =
DEPTNO = 10
EMPLOYEES = <>
)

Using these few simple steps allows us to build a virtual ODBMS based on the traditional RDBMS – and lift PL/SQL programming to the OO-programming level.

The OO-Model we have created here show some clear advantages:

• No loss of functionality as Oracle’s ODBMS suffers from


• A direct mappnig from relational data model to object model, which imposes absolutely no changes at the RDBMS and can easily be created using simple generators


• Simplification of DML statements


• Access to more complex Business Objects is independent of the underlying data sources


• Easy mapping of the generated OO-ROWTYPES and Business Objects to other OO programming languages such as Java. There are plenty generators to autmated the process of creating accessing stubs and classes

Still there are things to improve for future releases of Oracle:

• Oracle Object-Types do not support ROWID, %TYPE and INDEX BY TABLE


• A Type can not reference a container of its own TYPE


• Having Java-like Interfaces would improve OO-structring a lot. This would allow us to get rid of the otherwise unneeded OBJECT_NAME attribute from TYPE_OBJECT


• Once a type is derived from or is referenced from some other type, it can not be REPLACEd (only ALTER TYPE can do so). The current workaround uses DROP TYPE FORCE to do so.