Problem solve Get help with specific problems with your technologies, process and projects.

Simple Oracle objects

This tip describes how to build, use and maintain Oracle simple objects.

Version 8i introduced simple objects in Oracle and so made the Oracle database a true object-oriented environment. This article presents short scripts that demonstrate how to build, use and maintain Oracle simple objects.

Phase 1: The object type creation

Every object you create starts with an Oracle type creation, like this:

Create Or Replace Type Occupation as Object  
( start_date date,   
  end_date   date,   
  descr      varchar2(30),   
  avg_sal    number(7,0),   
  Member function job_duration return Number,   
  Member function inc_sal return number  

Phase 2: Object type body for methods implementation

Use this only where the function exists in the object's type definition.

Create Or Replace Type Body occupation as   
 member function job_duration return number is    
   return (self.end_date - self.start_date) ;    
 member function inc_sal return number is     
  f number;    
   select Decode (Lower(tri(self.descr)),  'analyst',.05, 'clerk',.03,.1) into f   from DUAL;       
   return (self.avg_sal * ( 1 + f));    
 end ;

Phase 3: Usage in tables

Use the object type simply as a "column" in the table's structure.

Create Table Jobs  (JobId number(2),    current_occupation occupation );/

Phase 4: Use in PL/SQL

Here is how to insert into a table containing objects:

declare i number;
  i := 0;  
  for ce in (select Job, Min(HireDate) as sDate, Max(HireDate) as 
              fDate, avg(sal) as avgsal from emp   
                group By Job ) loop      
     i := i + 1;      
     Insert into Jobs Values           
       (i, occupation (ce.sDate, ce.fDate, ce.Job, ce.avgSal) );  
  end loop;  

Phase 5: Use in SQL

This shows the usage in a simple SQL select statement. Note that a table alias is necessary; it will not work otherwise.

column sDate format a10
column fDate format a10
column duration format a10
column Incsal format a8
column Job format a12
column avgsal format a8
select j.Current_Occupation.Start_date as sDate,           
  j.Current_Occupation.end_date as fDate,        
  to_char(j.Current_Occupation.Job_duration()) as duration,       
  to_char(j.Current_Occupation.Inc_Sal()) as IncSal,        
  j.Current_Occupation.descr as Job,       
  to_char(j.Current_Occupation.avg_sal) as avgSal from Jobs j;

About the author

Eli Leiba works at the Israel Electric Company as a senior application DBA in Oracle and Microsoft SQL Server systems. He has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in computer science and has 11 years of experience working in the database field. Additionally, Mr. Leiba teaches SQL Server DBA and development courses at the Microsoft CTEC and serves as a senior database consultant for several Israeli start-up companies.


For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.


Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.