Introduction
In the context of Oracle Database, the term oracle class refers to an object type, which is a structured data type that defines a set of attributes and methods. An object type behaves analogously to a class in object‑oriented programming languages, allowing the database to store and manipulate entities that possess both data and behaviour. Oracle Database supports the creation, storage, and retrieval of objects in tables, enabling developers to model real‑world entities directly within the relational schema.
Object types were introduced in Oracle 9i as part of the object‑relational database (ORDB) extension. This feature bridges the gap between the relational model, which stores data in tables of rows and columns, and the object‑oriented paradigm, which groups related data and functions into reusable constructs. Oracle’s implementation of object types offers inheritance, polymorphism, encapsulation, and method overloading, allowing complex data structures to be represented with a high degree of expressiveness.
Historical Development
Early Oracle Versions
Before the advent of object‑relational capabilities, Oracle Database was strictly relational. The 8i release introduced several enhancements, such as global temporary tables and improved indexing, but it did not provide a mechanism to define complex data structures beyond simple scalar types. Users often had to resort to flat tables or custom application code to model hierarchical or composite data.
Object‑Relational Enhancements
The introduction of the Oracle Database 9i Release 1 in 2001 marked the first major foray into object‑relational concepts. Oracle added support for object types, collection types, and methods, effectively embedding an object‑oriented layer into the relational engine. The syntax for defining an object type mirrored that of class definitions in Java or C++, with attributes declared as data members and methods declared as stored procedures or functions that operate on the instance. The initial implementation also provided a new data type called TABLE OF to store collections of objects.
Oracle 10g and Beyond
Subsequent releases refined the object‑relational model. Oracle 10g added enhanced support for XML, making it possible to store and query XML data directly within object types. Oracle 11g introduced the concept of nested tables and VARRAYs as collection types, providing more efficient storage for large sets of elements. From Oracle 12c onwards, the database has expanded its capabilities to include polymorphic queries, improved performance for object type operations, and tighter integration with the Autonomous Database platform. Throughout these iterations, the core idea of representing domain entities as classes persisted, providing developers with a flexible modeling tool that blends relational and object‑oriented features.
Technical Foundations
Object Types as Classes
An object type in Oracle is defined using the CREATE TYPE statement. The definition includes a list of attributes and, optionally, a set of methods. For example, a simple PERSON type might be declared as:
CREATE TYPE PERSON AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
birth_date DATE,
ADDRESS ADDRESS_TYPE,
MEMBER_TYPE,
CONSTRUCTOR FUNCTION PERSON RETURN SELF AS RESULT,
MEMBER PROCEDURE DISPLAY
);
Each attribute corresponds to a column in a relational table that uses the type. The optional methods provide behaviour, such as validation or formatting logic. Methods can be either constructor functions, which are called automatically when an object is created, or member procedures that can be invoked by SQL or PL/SQL code.
Syntax and Semantics
The syntax for object type creation follows a declarative pattern:
CREATE TYPE type_name AS OBJECT (- attribute list, each separated by a comma, e.g.,
attributename datatype - optional method declarations, e.g.,
MEMBER PROCEDURE procedurename(paramlist) );
Once defined, an object type can be instantiated in PL/SQL using the constructor syntax: type_name(arg1, arg2, ...). In SQL, object columns are declared using the type name, e.g., CREATE TABLE employees (id NUMBER, emp_obj PERSON);. The table column emp_obj stores an instance of the PERSON type.
Inheritance and Polymorphism
Oracle supports single inheritance for object types. A type can extend another type, inheriting its attributes and methods:
CREATE TYPE EMPLOYEE AS OBJECT (
employee_id NUMBER,
hire_date DATE
) INHERITS (PERSON);
The EMPLOYEE type automatically includes all attributes of PERSON, plus its own attributes. Method overriding is also supported: an inheriting type can provide a new implementation of a method defined in the parent type. Polymorphic queries allow a reference of a parent type to point to an instance of a child type, enabling queries that operate on a common interface.
Collection Types and Multi‑valued Attributes
Object types can include attributes that are collections. Oracle provides two primary collection types: VARRAY and NESTED TABLE. A VARRAY is a bounded, ordered list, while a NESTED TABLE is an unbounded, potentially unordered collection. For example:
CREATE TYPE PHONE_NUMBERS AS TABLE OF VARCHAR2(15);
CREATE TYPE PERSON AS OBJECT (
first_name VARCHAR2(50),
last_name VARCHAR2(50),
phone_nums PHONE_NUMBERS
);
When a table column is declared with a collection type, Oracle stores the collection elements in a separate storage structure. Access to individual elements can be achieved using the INDEX BY clause or the SELECT ... FROM TABLE() syntax.
Method Definitions and Stored Procedures
Methods declared in an object type can be defined as separate PL/SQL procedures or functions. The syntax is similar to ordinary stored procedures, with the addition of the SELF keyword to refer to the instance on which the method is invoked. Example:
CREATE TYPE BODY PERSON AS
MEMBER PROCEDURE DISPLAY IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Name: ' || SELF.first_name || ' ' || SELF.last_name);
END;
END;
Methods can be invoked in SQL by using dot notation: SELECT emp_obj.DISPLAY FROM employees;. Oracle also allows method overloading, where multiple methods share the same name but differ in parameter lists.
Deployment and Usage
Creating Oracle Classes
To deploy an object type in a database, the CREATE TYPE statement is executed within a user schema. The type definition is stored in the data dictionary under ALL_TYPES. If the type needs to be shared across schemas, the user must grant appropriate privileges, or the type can be created in a shared schema such as SYS.
When a type is modified, the database requires recompilation of dependent objects. Oracle provides the ALTER TYPE statement for adding attributes, methods, or modifying the type’s characteristics. However, changing the data type of an existing attribute is restricted, as it could invalidate stored data. In such cases, the common approach is to create a new type and migrate data accordingly.
Instantiating Objects
Objects can be instantiated in PL/SQL using the constructor or by selecting from a table that contains the type. Example in PL/SQL:
DECLARE
p PERSON;
BEGIN
p := PERSON('John', 'Doe', DATE '1990-01-01', NULL);
DBMS_OUTPUT.PUT_LINE(p.first_name);
END;
In SQL, objects can be inserted into a table using the INSERT statement with constructor syntax:
INSERT INTO employees (id, emp_obj)
VALUES (1, PERSON('Jane', 'Smith', DATE '1985-05-12', NULL));
Persisting Objects in Tables
Object columns are treated like any other column in relational terms, but the storage engine handles the serialization of the object’s attributes. For collections, Oracle creates a secondary storage structure called an object collection. The main advantage of object tables is that they provide a natural mapping between application objects and database records, reducing the impedance mismatch that typically arises when using pure relational tables.
Indexing object attributes is possible using the INDEX BY REF clause for reference attributes or by creating function-based indexes on scalar attributes. For example:
CREATE INDEX idx_last_name ON employees(emp_obj.last_name);
Such indexes accelerate queries that filter on object attributes.
Querying Object Tables
SQL can retrieve object attributes using dot notation:
SELECT e.id, e.emp_obj.first_name, e.emp_obj.last_name
FROM employees e
WHERE e.emp_obj.birth_date < DATE '2000-01-01';
To query elements of a collection attribute, the TABLE() operator is employed:
SELECT e.id, phone
FROM employees e, TABLE(e.emp_obj.phone_nums) phone
WHERE phone > '555';
Polymorphic queries can reference the parent type, allowing retrieval of rows stored in tables that use child types:
SELECT *
FROM employees
WHERE emp_obj IS OF (EMPLOYEE);
These capabilities make object tables highly expressive while maintaining compatibility with standard SQL.
Object Relational Mapping Tools
Several third‑party ORMs (Object‑Relational Mappers) support Oracle object types, providing a higher‑level abstraction for developers. Examples include:
- Hibernate OGM – an extension of Hibernate that can map Java objects to Oracle object types.
- Apache OpenJPA – supports mapping of Java classes to Oracle object tables.
- Oracle Developer Studio – integrates with PL/SQL to auto‑generate object type definitions from Java classes.
These tools translate object‑oriented operations into SQL statements that leverage the underlying Oracle object model, thereby simplifying application development.
Comparison with Other Database Systems
Microsoft SQL Server
SQL Server supports user‑defined types (UDTs) primarily as table‑valued parameters or scalar user‑defined types. However, SQL Server does not natively store complex objects in the same way as Oracle. The XML data type offers some object‑like behaviour, but the impedance mismatch persists. Oracle’s object model provides richer inheritance and method capabilities that SQL Server lacks.
PostgreSQL
PostgreSQL offers record types and domains that can be used to encapsulate data, but they do not support methods or inheritance. The hstore extension provides key‑value storage, and the JSONB type allows flexible schema definitions. PostgreSQL’s approach is more lightweight compared to Oracle’s comprehensive object‑relational model.
MySQL
MySQL’s support for JSON columns has improved queryability, but it does not provide methods or inheritance. Object‑like modelling is generally achieved through ORM frameworks that translate object operations to pure relational queries. MySQL’s JSON implementation is more limited than Oracle’s XML‑aware object types.
Oracle Autonomous Database
The Autonomous Database leverages Oracle’s core object‑relational features while adding self‑optimizing capabilities. It supports the same CREATE TYPE syntax but also provides a serverless environment where performance tuning is automated. Unlike other platforms, the Autonomous Database can handle polymorphic queries and large collection types without manual partitioning.
Security and Access Control
Granting Privileges
Access to object types and object tables is controlled via standard database privileges. Users require SELECT, INSERT, UPDATE, and DELETE privileges on the owning schema. For methods, the EXECUTE privilege must be granted to invoke member procedures.
Reference attributes – columns that store references to other objects – can be secured by granting REFERENCES privileges on the referenced type. The SELECT ANY DICTIONARY privilege allows access to dictionary views such as ALL_TYPES.
Encryption and Data Masking
Oracle provides Transparent Data Encryption (TDE) for object attributes, ensuring that data stored within objects is encrypted at rest. Dynamic Data Masking can also be applied to object attributes by creating masking policies that automatically redact sensitive values when accessed by privileged roles. Example:
ALTER USER john WITH MASKING POLICY ON employees(emp_obj.first_name, emp_obj.last_name);
Such security features are particularly useful when storing personally identifiable information in object tables.
Auditing and Logging
Oracle’s fine‑grained auditing (FGA) can be configured on object attributes, enabling the capture of detailed access logs. For example:
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'employees',
policy_name => 'audit_emp_obj',
audit_condition => 'emp_obj.last_name = ''Smith''',
audit_column => 'emp_obj.last_name'
);
Such policies provide visibility into data access patterns, facilitating compliance with regulations like GDPR or HIPAA.
Future Trends and Extensions
Integration with Cloud Services
Oracle’s Autonomous Database introduces automated performance tuning and scaling. Object types can be defined as part of a managed service, allowing dynamic schema changes with minimal downtime. Integration with services such as Oracle Cloud Infrastructure Functions enables serverless functions to operate on object tables, opening new avenues for event‑driven architectures.
Graph Extensions
Oracle’s Graph features allow modeling of graph data structures using vertices and edges. Future iterations may incorporate object types as vertex or edge representations, combining relational, object‑oriented, and graph models in a single schema. This would enable developers to query complex relationships using graph traversal operators while preserving object semantics.
Machine Learning and AI Integration
Oracle has begun embedding machine learning capabilities directly into the database via the DBMS_AIR and ML packages. Object types can be used to store model metadata or training data, providing a cohesive framework for AI pipelines. For example, an ML_MODEL type could encapsulate model parameters, evaluation metrics, and training history, allowing the database to serve predictions directly through SQL.
Conclusion
Oracle’s object‑relational model offers a robust platform for representing domain entities as classes. Over the course of more than two decades, the technology has matured to provide powerful features such as inheritance, polymorphic queries, and native XML integration. By bridging the gap between relational and object‑oriented paradigms, Oracle object types facilitate cleaner application code, reduce the impedance mismatch, and enable advanced data modelling techniques. While other database systems provide similar capabilities, Oracle’s comprehensive support for object types and methods remains unique, especially when combined with its performance optimizations and cloud‑native features.
No comments yet. Be the first to comment!