We've already seen in the first tutorial on Oracle data types that the data types supported by Oracle fall into one of several categories:-
As we've already discussed the built-in data types, this tutorial will look in more detail at the user-defined types.
User-defined typesUser-defined Oracle data types can be categorised as
An Oracle database is an object-relational hybrid which means that it supports both objects and the traditional relational database features - tables and columns.
By providing support for objects in the database Oracle eliminates the need for object-oriented applications to translate their object data models into relational models as well as the need at run-time to map object access requests to relational table access.
Oracle Tips & Tricks to SKYROCKET Your Career!
If you're not already a subscriber you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.
An Oracle data type that is an object has 3 parts:
New object types are created using the CREATE OR REPLACE TYPE statement in SQL. This defines the name of the object and its attributes. Methods are defined as part of the CREATE TYPE BODY statement;
For example to create new type called PERSON you might define it like this:
CREATE OR REPLACE TYPE person IS OBJECT
MEMBER FUNCTION name RETURN VARCHAR2);
Where address_type is another user-defined type and the function name is a method the detail of which is defined by the CREATE TYPE BODY statement like this:
CREATE TYPE BODY person IS
MEMBER FUNCTION name RETURN VARCHAR2 IS
RETURN first_name||' '||last_name;
The methods that can be defined for object Oracle data types include:
Note that an object type can have either a MAP function or an ORDER function, not both.
It is also worth noting that object types can be used in relational tables as well as object tables.
For example, we could use the Oracle data type Person that we've just defined in a relational table declaration like this:
CREATE TABLE customers (cust_id Number(12), cust person);
To create an object table using this type the declaration would be like this:
CREATE TABLE customers OF person;
In this case, each row of the table contains an instance of type Person. The instance is automatically assigned a unique, system-generated object identifier when a row is inserted.
Nested table Oracle data types are useful where you have a master-detail/parent-child relationship between 2 entities and where the number of detail records is unknown. A good example of this is invoices and invoice lines.
For example, to create, an invoice table with a nested invoice-line table, first define the object type for the invoice details:
CREATE OR REPLACE TYPE inv_dtl AS OBJECT
Next, define the table type based on the object type:
CREATE OR REPLACE TYPE inv_dtl_tab AS TABLE OF inv_dtl;
Finally define the parent table with the nested table:
CREATE TABLE invoice(
NESTED TABLE lines STORE AS invoice_lines;
The STORE AS clause defines the name of the segment used to hold the nested table and is necessary because nested tables are stored in a different segment to (but in the same tablespace as) the main table.
To access a nested table, you have to un-nest the table:
SELECT invoice_id, inv_line_id, item_desc, item_amt
FROM invoice i, TABLE(i.lines)
WHERE invoice_id = 1;
A VARRAY is an Oracle data type that is useful when the maximum number of detail records is small and is known in advance (such as a list of phone numbers for a customer e.g. office, home and mobile). Note that there is no storage clause for the varray, because they are stored in line.
CREATE TYPE phone_no_type AS OBJECT
( Desc VARCHAR2(20)
CREATE TYPE phone_no_list AS VARRAY(3) OF phone_no_type;
CREATE OR REPLACE TYPE cust
AS OBJECT (
The elements of a varray cannot be accessed individually using SQL, but they can be accessed in PL/SQL and 3GLs.
The REF Oracle data type is used to establish a relationship from one object to another by storing the object identifier (the object's unique identifier) of another object. This relationship is defined by means of a reference in the object table definition. This acts like a foreign key, linking one object to another object in the database (via the unique object identifier – the object’s primary key). Oracle uses the link automatically to perform the same function as a join between relational tables.
For example consider a new object called inv which in a relational database would have a foreign key relationship to the customer table. In object terms this becomes a new object referencing an existing object as follows:
CREATE OR REPLACE TYPE inv AS OBJECT
,Client REF cust
CREATE TABLE invoice OF inv (id PRIMARY KEY);
When a REF value points to a nonexistent object, the REF is said to be "dangling" (not the same as a null REF). The condition IS [NOT] DANGLING is used to determine whether or not a REF is dangling.
For example, using the object Oracle data type CUST as just defined and the table INVOICE the following statement would retrieve all of the names of customers who have a record in the table.
FROM invoice i
WHERE i.client IS NOT DANGLING;
Looking for Oracle training?
With our partners we offer instructor-led Oracle training on or off site in the UK as well as training via the Internet. See here for Oracle training in the UK. Or contact us for details of our self-led on-line training courses.