Flat 10% & up to 50% off + Free additional Courses. Hurry up!

PL/SQL with Object Types


Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.


Declaring and Initializing Objects in PL/SQL

An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list.

Currently, you cannot define object types in a PL/SQL block, subprogram, or package. You can define them interactively in SQL*Plus using the SQL statement CREATE TYPE.

After an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.

 Example: Working With Object Types

 CREATE TYPE address_typ AS OBJECT (

street VARCHAR2(30),

city VARCHAR2(20),

state CHAR(2),

postal_code VARCHAR2(6) );




Declaring Objects in a PL/SQL Block

You can use object types wherever built-in types such as CHAR or NUMBER can be used.

Example:  Declaring Object Types in a PL/SQL Block


emp employee_typ; -- emp is atomically null


-- call the constructor for employee_typ

emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',

'555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,

address_typ('376 Mission', 'San Francisco', 'CA', '94222'));

DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details

emp.display_address(); -- call object method to display details



You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type employee_typ to specify the datatype of a formal parameter:

PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...

In the following example, you use object type employee_typ to specify the return type of a function:

FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...


 Manipulating Objects in PL/SQL

 It describes how to manipulate object attributes and methods in PL/SQL.

1. Accessing Object Attributes With Dot Notation

To access or change the value of an attribute, you use dot notation. Attribute names can be chained, which lets you access the attributes of a nested object type. For example:

Example: Accessing Object Attributes


emp employee_typ;


emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',

'555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110,

address_typ('376 Mission', 'San Francisco', 'CA', '94222'));

DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name);


DBMS_OUTPUT.PUT_LINE(emp.address.city || ', ' ||emp. address.state || ' ' ||





2. Calling Object Constructors and Methods

Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression.

Example: Inserting Rows in an Object Table


emp employee_typ;


INSERT INTO employee_tab VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON',

'555.111.2222', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110,

address_typ('123 Main', 'San Francisco', 'CA', '94111')) );

INSERT INTO employee_tab VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN',

'555.111.3333', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110,

address_typ('123 Broadway', 'Redwood City', 'CA', '94065')) );




3. Updating and Deleting Objects

From inside a PL/SQL block you can modify and delete rows in an object table.

Example: Updating and Deleting Rows in an Object Table


emp employee_typ;


INSERT INTO employee_tab VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS',

'555.111.2277', '07-NOV-04', 'SA_REP', 8800, .12, 101, 110,

address_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) );

UPDATE employee_tab e SET e.address.street = '1040 California'

WHERE e.employee_id = 370;

DELETE FROM employee_tab e WHERE e.employee_id = 310;




Defining SQL Types Equivalent to PL/SQL Collection Types

To store nested tables and varrays inside database tables, you must also declare SQL types using the CREATE TYPE statement. The SQL types can be used as columns or as attributes of SQL object types.

You can declare equivalent types within PL/SQL, or use the SQL type name in a PL/SQL variable declaration.

Example: Declaring a Nested Table in SQL

 CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type


CREATE TYPE student AS OBJECT ( -- create object

id_num INTEGER(4),

name VARCHAR2(25),

address VARCHAR2(35),

status CHAR(2),

courses CourseList); -- declare nested table as attribute


CREATE TABLE sophomores of student

NESTED TABLE courses STORE AS courses_nt;

The identifier courses represents an entire nested table. Each element of courses stores the name of a college course such as ‘Math 1020′.


PL/SQL Collections with SQL Object Types

Collections let you manipulate complex data types within PL/SQL. Your program can compute subscripts to process specific elements in memory, and use SQL to store the results in database tables. The NESTED TABLE clause is required whenever a database table has a nested table column. The clause identifies the nested table and names a system-generated store table, in which Oracle stores the nested table data.

Within PL/SQL, you can manipulate the nested table by looping through its elements, using methods such as TRIM or EXTEND, and updating some or all of the elements. Afterwards, you can store the updated table in the database again. You can insert table rows containing nested tables, update rows to replace its nested table, and select

nested tables into PL/SQL variables. You cannot update or delete individual nested table elements directly with SQL; you have to select the nested table from the table, change it in PL/SQL, then update the table to include the new nested table.

"0 Responses on PL/SQL with Object Types"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.


Sales Offer

  • To avail this offer, enroll before 23rd October 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer


Sign Up or Login to view the Free PL/SQL with Object Types.