Learning Objectives – after this chapter you will be able to:
- Explain object types, objects, attributes and methods.
- Create object types and type bodies, use constructors and SELF.
- Use object types as columns (column objects) and access attributes/methods from SQL and PL/SQL.
- Understand type dependencies, object tables, OIDs, REF and DEREF.
- Enable object comparison with MAP or ORDER member functions.
Introduction to Object Types
An object type is a user-defined composite datatype that contains the data and code for an entity – used to model real-world entities such as a bank account or a customer address. It consists of attributes (data) and methods (operations on the data). An instance of an object type is an object. The type is only a template and occupies no space.
CREATE [OR REPLACE] TYPE type_name AS OBJECT ( attribute datatype [, attribute datatype] ... [, MEMBER {PROCEDURE | FUNCTION} specification] ... [, {MAP | ORDER} MEMBER FUNCTION specification] );Creating and using an object type
create or replace type project_type as object
( name varchar2(20),
stdate date );
/
create table emp
( empno number(5),
ename varchar2(20),
project project_type ); -- column object
insert into emp values (1, 'Larry', project_type('Billing system', '12-dec-2000'));
select project from emp; -- PROJECT_TYPE('Billing system', '12-DEC-00')- An object type can define: columns of relational tables, PL/SQL variables, subprogram parameters, attributes of another object type (nested objects) and object tables.
- Oracle creates a constructor – a procedure with the same name as the type – that must be given one value per attribute (give NULL explicitly when needed).
- A column whose type is an object type is a column object. DESCRIBE shows a type’s structure; USER_TYPES and USER_TYPE_ATTRS describe types.
- Only the owner can use a type unless he grants EXECUTE on it; others then use
srikanth.project_type.
Methods and SELF
Methods are declared in CREATE TYPE with MEMBER and defined in CREATE TYPE BODY (needed only when the type has methods). Inside a method, attributes are used without a qualifier because each method implicitly receives a reference to the invoking object, called SELF (stdate = self.stdate).
Type with methods; accessing attributes and methods from SQL
create or replace type project_type as object
( name varchar2(20),
stdate date,
member function getage return number,
member procedure change_stdate (newstdate date) );
/
create or replace type body project_type is
member function getage return number is
begin
return sysdate - self.stdate;
end;
member procedure change_stdate (newstdate date) is
begin
if newstdate > sysdate then
raise_application_error(-20011, 'Invalid starting date');
else
stdate := newstdate;
end if;
end;
end;
/
select e.project.name, e.project.getage() from emp e; -- alias is required
update emp e set e.project.name = 'offers.com' where empno = 1; In SQL, attributes and methods must be prefixed with a table alias:
select project.name from emp fails with ORA-00904, while select e.project.name from emp e works.Dependencies, Object Tables and References
- A table with a column of an object type, or a type with an attribute of another type, depends on that type. A type with dependents cannot be dropped (ORA-02303: cannot drop or replace a type with type or table dependents) unless DROP TYPE … FORCE is used, which makes dependents invalid.
- An object table is a table whose every row is an object (row object); the type’s attributes become its columns, so it can be used like a relational table and can have constraints.
- Each row object has an object identifier (OID) – system-generated or derived from the primary key (
OBJECT ID PRIMARY KEY). - A REF attribute stores a reference to a row object; REF(alias) obtains it and DEREF returns the object it points to.
Object table, REF and DEREF
create table projects of project_type ( name primary key );
insert into projects values ( project_type('Billing System', sysdate) );
insert into projects values ( 'Hotel Management', '10-dec-2001' ); -- without constructor
update projects set stdate = sysdate where name = 'Billing System';
create type emp_type as object
( empno number(5),
ename varchar2(20),
project ref project_type );
/
create table emp_obt of emp_type;
insert into emp_obt
select 1, 'Praneeth', ref(p) from projects p where p.name = 'Billing System';
select empno, ename, deref(project) from emp_obt;Objects in PL/SQL
declare
proj project_type;
begin
proj := project_type('seconds.com', '12-jun-2001'); -- constructor
dbms_output.put_line( proj.getage() );
end;MAP and ORDER Member Functions
Objects can be compared with >, < etc. (and used in ORDER BY) only if the type has a MAP or an ORDER member function. Without either, only equality/inequality comparisons are possible. A type may have one or the other, not both.
| MAP MEMBER function | ORDER MEMBER function |
|---|---|
| Maps each instance to a value of a scalar type (e.g. returns STDATE); called implicitly to order instances | Takes another instance as an explicit argument and returns a negative, zero or positive number (SELF less than, equal to or greater than it) |
map member function map_stdate return date | order member function ord_function(obj project_type) return number – only one per type, return type NUMBER |
With either function, select ename from emp order by project; sorts employees by their project’s starting date.
Key Takeaways
- Object type = attributes + methods; object = instance; constructor has the type’s name.
- Methods are defined in CREATE TYPE BODY and reach attributes through SELF.
- Use a table alias to access object attributes and methods in SQL.
- Object tables store row objects with OIDs; REF/DEREF link objects; MAP/ORDER enable comparison.
Chapter 22 Notes (PDF)
ch22.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 22 notes (PDF). Click a question to see the answer.
Answer : An object type is a composite, user-defined datatype that contains the data and code for a particular entity and is used to model real-world entities such as a bank account or a customer’s address. It consists of data attributes and methods. An instance of an object type is called an object.
Answer : No – an object type is a template and does not occupy any space. It is created with CREATE TYPE, e.g. CREATE OR REPLACE TYPE project_type AS OBJECT (name VARCHAR2(20), stdate DATE), and can then be used like scalar datatypes such as NUMBER or CHAR.
Answer : To define columns of relational tables, variables in PL/SQL blocks, parameters of PL/SQL subprograms, data attributes of another object type (nested objects) and object tables.
Answer : USER_TYPES contains details of user-defined datatypes and USER_TYPE_ATTRS gives detailed information about each type’s attributes. The DESCRIBE command displays the structure (attributes and methods) of an object type.
Answer : EXECUTE. By default only the owner can refer to an object type; the owner grants EXECUTE (e.g. GRANT EXECUTE ON project_type TO praneeth), after which the other user refers to it with the schema prefix, e.g. srikanth.project_type.
Answer : Every object type has a constructor created by Oracle – a procedure with the same name as the object type that initialises its attributes. It must be passed as many parameters as there are attributes, e.g. project_type('Billing system', '12-dec-2000'); NULL must be given explicitly for an attribute that should be null.
Answer : When a column of a relational table is of an object type – e.g. PROJECT of type PROJECT_TYPE in EMP – the column is called a column object; each row contains an object of that type.
Answer : A method is declared in CREATE TYPE using the MEMBER option (MEMBER FUNCTION or MEMBER PROCEDURE) and defined in the CREATE TYPE BODY command. CREATE TYPE BODY is required only when the object type contains methods.
Answer : Each method is implicitly passed a reference to the object that invokes it; this reference is called SELF. That is why attributes can be accessed inside methods without a qualifier – stdate and self.stdate mean the same.
Answer : Using the dot operator with a table alias prefix, e.g. SELECT e.project.name, e.project.getage() FROM emp e. Without the alias (SELECT project.name FROM emp) SQL does not recognise the attribute and gives ORA-00904: invalid column name.
Answer : A table with a column of an object type, or an object type with an attribute of another type, is dependent on that type. A type with dependents cannot be dropped (ORA-02303: cannot drop or replace a type with type or table dependents) unless the FORCE option of DROP TYPE is used, which makes the dependent objects invalid.
Answer : An object table is a table whose rows are objects (row objects), e.g. CREATE TABLE projects OF project_type. The attributes of the type map to the columns of the table, so rows can be inserted with or without the constructor, queried, updated and deleted like a relational table, and constraints such as a primary key can be defined.
Answer : Each row object in an object table is assigned an object identifier by Oracle. The OID is either system generated or the primary key of the table serves as the OID (specified with OBJECT ID PRIMARY KEY in CREATE TABLE). A row object can be referenced using its OID.
Answer : An attribute declared as REF type (e.g. project REF project_type) stores a reference to a row object. The REF operator gets the reference of a row object, e.g. SELECT 1, 'Praneeth', REF(p) FROM projects p WHERE ...; DEREF de-references a reference to get the object it points to, e.g. SELECT empno, ename, DEREF(project) FROM emp_obt.
Answer : Both allow objects to be compared with operators like > and < and used in ORDER BY. A MAP member function returns the relative position of an instance by mapping it to a value of a predefined scalar type (e.g. returns STDATE) and is called implicitly. An ORDER member function takes another instance as an explicit argument and returns a negative, zero or positive number indicating that SELF is less than, equal to or greater than it; there can be only one, with return type NUMBER. A type can have either MAP or ORDER, not both; with neither, only equality or inequality comparisons are possible.
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database Object-Relational Developer’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adobj/
- Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/