Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Object Types in PL/SQL


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

Oracle implements Objects through the use of TYPEs, defined in a similar way to packages.  Unlike packages in which the instance of the package is limited to the current session, an instance of an object type can be stored in the database for later use. 

The definition of the type contains a comma separated list of attributes/properties, defined in the same way as package variables, and member functions/procedures.   If a type contains member functions/procedures, the procedural work for these elements is defined in the TYPE BODY.

To see how objects can be used, suppose an object is created to represent a person.  In this case, a person is defined by three attributes: first_name, last_name and date_of_birth.  Returning the age of the person is also desired, so this is included as a member function, get_age.

CREATE OR REPLACE TYPE t_person AS OBJECT (
  first_name     VARCHAR2(30),
  last_name      VARCHAR2(30),
  date_of_birth  DATE,
  MEMBER FUNCTION get_age RETURN NUMBER
);
/

Type created.

Next the type body is created to implement the get_age member function.

CREATE OR REPLACE TYPE BODY t_person AS
  MEMBER FUNCTION get_age RETURN NUMBER AS
  BEGIN
    RETURN tRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
  END get_age;
END;
/

Type body created.

Once the object is defined, it can be used to define a column in a database table.

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  t_person
);

Table created.

To insert data into the PEOPLE table, the t_person() constructor must be used. This can be done as part of a regular DML statement or using PL/SQL.

INSERT INTO people (id, person)
VALUES (1, t_person('John', 'Doe', TO_DATE('01/01/2000','DD/MM/YYYY')));

1 row created.

COMMIT;

Commit complete.

DECLARE
  l_person  t_person;
BEGIN
  l_person := t_person('Jane','Doe', TO_DATE('01/01/2001','DD/MM/YYYY'));
  INSERT INTO people (id, person)
  VALUES (2, l_person);
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Once the data is loaded, it can be queried using the dot notation:

  • alias.column.attibute

  • alias.column.function()

The query below shows this in action.

SELECT p.id,
       p.person.first_name,
       p.person.get_age() AS age
FROM   people p;

        ID PERSON.FIRST_NAME                     AGE
---------- ------------------------------ ----------
         1 John                                    5
         2 Jane                                    4

2 rows selected.

Collections in PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. There are three basic collections types, each using their own methods.

Associative Arrays in PL/SQL (Index-By Tables)

Associative Arrays have no upper bounds allowing them to constantly extend.  Originally the collection could only be indexed by a BINARY_INTEGER, although VARCHAR2 indexes were introduced in Oracle 9.2. Regardless of the index type, the index values do not need to be consecutive.  The collection is extended by assigning values to an element using an index value that does not currently exist.  The code below gives a brief example of how an associative array might be manipulated.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_collection IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER; 

  l_coll  t_collection;
  l_idx   NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    l_coll(i) := i;
  END LOOP load_loop; 

  -- Delete the third item of the collection.
  l_coll.DELETE(3); 

  -- Traverse sparse collection
  l_idx := l_coll.FIRST;
  << display_loop >>
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
    l_idx := l_coll.NEXT(l_idx);
  END LOOP display_loop;
END;
/
The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

Nested Table Collections in PL/SQL

Unlike associative arrays, nested table collections do not have an index value and can be stored in a database column.  In addition, some DML operations are possible on nested tables when they are stored in the database. 

During creation the collection must be dense, having consecutive subscripts for the elements.  Once created, elements can be deleted using the DELETE method to make the collection sparse.  The NEXT method overcomes the problems of traversing sparse collections.  The following code is a repeat of the previous example but coded to use a table collection.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE t_collection IS TABLE OF NUMBER(10);

  l_coll  t_collection;
  l_idx   NUMBER;
BEGIN

  -- Initialise the collection with two values.
  l_coll := t_collection(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    l_coll.extend;
    l_coll(l_coll.last) := i;
  END LOOP load_loop; 

  -- Delete the third item of the collection.
  l_coll.DELETE(3); 

  -- Traverse sparse collection
  l_idx := l_coll.FIRST;
  << display_loop >>
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
    l_idx := l_coll.NEXT(l_idx);
  END LOOP display_loop;
END;
/
The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
  
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.