Object Relational Database Features Implemented with Oracle

Oracle is a modern and feature-full database that supports many features of an object relational database system. Oracle supports type definition, type inheritance, collection type, member function, nested table, association and aggregation relationships. In this article, I have implemented all features other than association and aggregation relationships.

1. Database Structure

I have considered a database for a simple ecommerce site. The database will consist of different type of products having a bunch of attributes inherited from Products table whereas each table will have some other attributes of its own. So here I have a product object and a Clothes table that inherits the product object. Each clothe is available in multiple sizes. Further, each clothe tuple is available in multiple colors represented by a collection array in this example. For simplicity, I have included only one such table that inherits from products table where there could be other tables like Jewellery, Bags, Cosmetics and so on. Further, each product has multiple categories which is implemented by a nested category table. In our example, we charger 10% extra on the price of each product. Hence I have used a member function that returns 10% extra of the price.

2. Object Relational Structure

Product is an object, not the table, from which another object or type ClotheType is inherited. Product also contains a nested table Category so that each product row contains a reference to an instance of the table Category. Finally we create a table Clothes of the type ClotheType. ClotheType consists of two attributes: clothesize and clothecolor where clothesize is another Type and clothecolor is a collection array.

The object oriented concept of function has been included in ClotheType object which we call member function in Oracle.

3. DDL Script for the Database Objects

Create a type CategoryType

Create or replace type CategoryType as object(
categoryid int,
categoryname varchar2(50)
) not final

Create a table of CategoryType

Create or replace type ProductCategory as Table of CategoryType

Create an inheritable (specified by "not final" construct) object Product

Create or replace type Product as object(
productid int,
productname varchar2(100),
price number(7,2)
) not final

Create type ProductSize

Create or replace type ProductSize as object(
sizecode varchar2(20),
sizevalue varchar2(20)
)

Create collection array for colors

create or replace type ColorArray as varray(20) of varchar2(20)

Create a type ClotheType that inherits from Product

Create or replace type ClotheType under Products(
clothesize productsize,
clothecolor colorarray,
clothecategory productcategory,
member function total_price return number
)

--define the body for the member function total_price
create or replace type body ClotheType as
 member function  total_price return number is
 begin
                 return (price + price * 0.1);  
 end;
end;

Finally create a table of the type ClotheType

create or replace table Clothes of clothetype
nested table clothecategory store as prod_cat

4. Inserting rows into the Clothes table
Now we insert rows into the Clothes table.

  
   insert into clothes
   values(3,'V-neck tshirts with full sleeve',250,
   productsize('M','Medium'),
   colorarray('White','Blue','Black'),
          productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
   )
      );
   
   insert into clothes
   values(2,'Round-neck tshirts',450,
   productsize('L','Large'),colorarray('Red','Black','Green'),
          productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
      )
      );
     
   insert into clothes
   values(1,'Both side design tshirts',600,
   productsize('S','Small'),colorarray('Red','Blue','Black'),
             productcategory(categorytype('1','T-Shirts'),
                     categorytype('2','Men''s Wears')
      )
      );    

5. Querying the table Clothes
a. Normal query to select the columns productid, productname, pirce and productsize's columns sizecode and sizevalue

select c.productid,c.productname,c.price,
 c.clothesize.sizecode,c.clothesize.sizevalue
from clothes c

b. Querying the categories in a product from the nested table clothecategory

select c.productid,c.productname,p.categoryid,p.categoryname
 from clothes c,table(c.clothecategory) p
where c.productid=1;

select c.productid,c.productname,p.categoryid,p.categoryname
 from clothes c,table(c.clothecategory) p
where c.productid=2;

c. Querying the colors associated with a product

select c.productid, c.productname,p.*
 from clothes c, table(c.clothecolor) p
where c.productid=2;

d. Querying the total price by calling the member function total_price

select p.productid, p.productname,p.price,p.total_price() "Total Price"
 from clothes p;

6.Conclusion

we realized and implemented a bunch of object relational features of Oracle database. Some of the features we worked with were type inheritance, type as a column, datatype collection and nested table, as well as member function. We developed DDL script to create such objects and performed select queries. All in all, we got the good glimspe of object oriented programming in Oralce database.

Create unique id in oracle table using trigger and sequence

In one of my recent projects, I had to insert into a table where one column had to be unique and auto generated. Further I also had to maintain the data so that it was in serial with previously inserted data.

In oracle this can be accomplished by the combined use of Trigger and Sequence. I am going to describe the technique in this post.

Sequence: A sequence can generate unique number. User can limit the minimum and maximum value and also the starting value of the generated number. In other words a sequence is like a unique number generator whose nature can be controlled by a user. One sequence can be maintained per table so that we can provide unique and serial id for each table.

Trigger: A trigger can be fired before insert, update or delete operations in any table. This can be designed to help apply business logic while using data manipulations using insert, update and delete plsql programming.

Create a sequence

CREATE SEQUENCE SEQ_EMPLOYEE INCREMENT BY 1 START WITH 10000 MINVALUE 10000 MAXVALUE 99999 NOCYCLE NOCACHE NOORDER;

Create a Trigger on Employee Table

CREATE OR REPLACE TRIGGER TRIGGER_EMPLOYEE
BEFORE INSERT ON EMPLOYEE
FOR EACH ROW
begin
select SEQ_EMPLOYEE.nextval into :new.EMPLOYEE_ID from dual;
end;

Insert into Employee Table

INSERT INTO Employee (Name, Address, Post)
VALUES ('sangam','New Road-10', 'Software Developer')

Note: Since the trigger fetches unique number from sequence and inserts into Employee_id field, we need not to put this column in Insert statement as above.

Happy Oracling!!

Create New User In Oracle

In my previous post, I mentioned how to change the password of the default user that is created during the installation of the Oracle database. Now here I will put the simple but mention-worthy PL/SQL programming to create a new user in Oracle.

Steps of Creating New User in Oracle

1. Create a new user providing user name and password.

Connect to the Oracle SQLPLUSW. Run the following code:

CREATE USERNAME IDENTIFIED BY PASSWORD;

Example:

CREATE USER sams
IDENTIFIED BY myoracle;

While creating the user, you can define the default tablespace and default temporary space also. The combined syntax will then be like below:


CREATE USER sams
IDENTIFIED BY myoracle
DEFAULT TABLESACE system
TEMPORARY TABLESPACE temp;


2. Grant the user with essential privileges. One should consider well before granting access to the newly created user. The privileges can be assigned in many layers. For example you can assign the user with the DBA administrative privilege. On the other hand you can also limit the general user with the SELECT, INSERT, UPDATE or DELETE privileges to certain table(s).

The syntax follows:

GRANT CONNECT, RESOURCE to sams

GRANT DBA to sams

GRANT (SELECT INSERT UPDATE DELETE) ON TableName to UserName;

Example:
GRANT SELECT ON Employees to sams;

Happy Oracling!