PL/SQL Object Oriented concepts

Use PL/SQL Object Oriented concepts to reduce cost and time to  develop complex applications. Object Oriented concepts are mainly based on object types .

PL/SQL Object Oriented concepts Create/Replace  PL/SQL Object Types :

PL/SQL Object Oriented concepts Alter an Object type :
PL/SQL Object Oriented concepts Drop  an object type :
PL/SQL Object Oriented concepts Type Inheritance :
PL/SQL Object Oriented concepts Methods :


PL/SQL Object Oriented concepts Create/Replace  PL/SQL Object Types :

An object type combines attributes and methods(functions and procedures) into a single programming construct. The object type construct allows programmers to define their own reusable data types for use in PL/SQL programs and table and column definitions. An object type must be created in a database before it can be used in a PL/SQL program.

An object type has two parts , specification  and body.

PL/SQL Object Oriented concepts  specification: The specification is required and contains the attributes and methods specifications

Attribute specifications must appear before method specifications.

Object attributes, like table columns, are defined with a name and a data type.

The name can be any legal identifier,and the data type can be almost any data type known to SQL other than LONG, LONG RAW, ROWID, and UROWID.

Method headers appear in the object type specification in a comma-delimited list but not semicolons.



CREATE [OR REPLACE] TYPE <obj_type_name>[ AUTHID { CURRENT_USER | DEFINER } ]{ { IS | AS } OBJECT | UNDER parent_type_name }(

/*Variablr declaration*/

name_of_the_attribute1 datatype,

name_of_the_attribute2 datatype,

/*Method specification*/


INSTANTIABLE ] method_spec,…,][PRAGMA RESTRICT_REFERENCES(program_name, purities)]





From the syntax method_spec  any of the  following:





{ORDER | MAP} MEMBER FUNCTION comparison_function_spec



RETURNING SELF AS RESULT constructor_function_spec


PL/SQL Object Oriented concepts  Body :It contains the implementation for the methods or procedures .

The syntax for creating the object type body is:



CREATE [OR REPLACE] TYPE BODY obj_type_name{ IS | AS }(method_implementation; // Implement all methods and procedures here);


method_implementation is any of the following:





{ ORDER | MAP } MEMBER FUNCTION comparison_function_body



RETURNING SELF AS RESULT constructor_function_body

PL/SQL Object Example:

Object specification :

Object body:

Object instantiation and method call :




PL/SQL Object Oriented concepts Alter an Object type :


//Add new attribute to the existing object
ALTER TYPE < object_name>ADD ATTRIBUTE (<attribute_name>  <attribute_type> (size) , …)CASCADE INCLUDING TABLE DATA;//Add new function or procedure to the existing objectALTER TYPE < object_name>ADD MEMBER PROCEDURE/FUNCTION <procedure_name / function_name>CASCADE INCLUDING TABLE DATA;// Drop attribute from the existing objectALTER TYPE < object_name>DROP ATTRIBUTE (<attribute_name> ,…)CASCADE INCLUDING TABLE DATA;


After adding a method to a spec, you would use CREATE OR REPLACE TYPE BODY to implement it in the body

(include all the other methods as well).

There are a variety of restrictions on modifying types; for example, you cannot change a type from INSTANTIABLE to NOT INSTANTIABLE if you have created tables that depend on the type.


PL/SQL Object Oriented concepts Drop  an object type :


DROP  TYPE type_name [FORCE];

You can drop only an object type that has not been implemented in a table (or you can drop the tables first). The FORCE option will drop object types even if they have dependencies, but FORCE will irreversibly invalidate any dependent objects such as tables. FORCE does not do a DROP CASCADE. If you are dropping a type whose parent type has table dependents, this form of the statement:


DROP TYPE subtype_name VALIDATE;

That is, the database will perform the drop only if there are no objects of the subtype in any substitutable columns of the parent type.


PL/SQL Object Oriented concepts Type Inheritance :

You can define subtypes of object types following a single inheritance model. The database does not have a master root-level object; instead, each type is “standalone” unless declared otherwise.

The UNDER keyword specifies that the type exists as a subtype in a hierarchy. When you are using UNDER, the parent type must be marked NOT FINAL. By default, types are FINAL, meaning that you cannot declare a subtype of that type. A subtype contains all the attributes and methods of its parent (super type), and may contain additional attributes and methods. Methods can override corresponding methods from the parent.

Changes to the super type—such as the addition of attributes or methods—are reflected in the subtypes automatically.By default, object types are INSTANTIABLE —that is, an invoking program may create an object of that type. The phrase NOT INSTANTIABLE indicates that you don’t want any objects of the type, in which case the database will not create a constructor for it. This variation generally makes sense only with types that will serve as parents of other types.


When defining methods in a subtype, you have two options  : you can inherit a super type’s method, or you can override a super type’s method by defining your own subtype method of the same name and parameter list. If you choose to inherit,you do not need to write any code in the subtype. To override a super type, you must use the OVERRIDING.

The database supports dynamic method dispatch, also known as dynamic polymorphism, to determine which overridden method to invoke at run time. That is, it will choose the method in the most specific subtype associated with the currently instantiated object



PL/SQL Object Oriented concepts Methods :

There are four kinds of methods: member, static, constructor,

and comparison.

PL/SQL Object Oriented concepts  Member methods

A member method is a procedure or function designated with the keyword MEMBER. Calling programs may invoke such a method only on objects that have been instantiated.

PL/SQL Object Oriented concepts  Static methods A static method has no access to a current (SELF) object. Such a method is declared using the keyword STATIC and can be invoked at any time using type.method syntax.

PL/SQL Object Oriented concepts  Constructor methods

Even if you don’t declare any methods, every instantiable object has a default constructor method that allows a calling program to create new objects of that type. This built-in


• Has the same name as the object type

• Is a function that returns an object of that type

• Accepts attributes in named or positional notation

• Must be called with a value (or NULL) for every

attribute—there is no DEFAULT clause for object attributes

• Cannot be modified

You can replace this default constructor with your own using the CONSTRUCTOR FUNCTION syntax. This method must have the same name as the object type, but there are no restrictions on its parameter list. The RETURN clause of the constructor’s header must be RETURN SELF AS RESULT. The database supports overloading of programmer-defined constructors. All non static methods have the implied parameter SELF, which refers to the current instance of the object.The default mode for the SELF parameter is IN for functions and IN OUT for procedures. A programmer can alter the mode by explicitly including SELF in the formal parameter list.


PL/SQL Object Oriented concepts Comparison methods :

The comparison methods, ORDER and MAP, establish ordinal positions of objects for comparisons such as “<” or “between” and for sorting (ORDER BY, GROUP BY, DISTINCT). The database invokes a comparison method automatically whenever it needs to perform such an operation. MAP and ORDER methods are actually special types of member methods—that is, they execute only in the context of an existing object.



ORDER function : It accepts two parameters , SELF and another object of the same type. It must return an INTEGER value as explained in the following table:

Return value Object comparison
Any negative integer (commonly –1)  SELF < second object
Zero (0)  SELF = second object
Any positive integer (commonly 1)  SELF > second object


NULL Undefined comparison: attributes needed for the comparison are NULL

Example for ORDER function :

MAP function : It accepts no parameters and returns a scalar data type such as DATE, NUMBER, or VARCHAR2 for which the database already knows a collating sequence. The MAP function translates, or maps, each object into this scalar data type space. If no ORDER or MAP function exists for an object type, then SQL, but not PL/SQL, supports only limited equality comparisons of objects. Objects are equal if they are of the same object type and if each attribute is equal. Use MAP if possible when frequently sorting or comparing a large number of objects, as in a SQL statement; an internal optimization reduces the number of function calls. With ORDER, the function must run once for every comparison.

Example for MAP function :

You also can define a method to be NOT INSTANTIABLE , which means that you specify only the header of the method, but you do not need to provide an implementation in the

• If entire object type defined as NOT INSTANTIABLE , which means that you cannot instantiate an instance from this type. You can use it only as a super type in an object type hierarchy.

This is also possible inside the implementation sections of subtype methods using SELF:

(SELF AS parent_type).method_invocation;

After an object is initialized, it can be stored in the database, and you can then locate and use that object with the REF, VALUE, and DEREF operators.


Related Posts :
What is an object in java with an example
   [will not be published]

^ <