Object Programming in Oracle

This article demonstrate how to code object oriented program in oracle. An employee object type is created with two properties EMP_ID and EMP_NAME. Another type TBL_EMP is created used to hold employees. Next employee table named emp is created that has same properties as object OBJ_EMP.

The procedure INSERT_EMP takes TBL_EMP type as its only IN parameter. If the count is greater than zero the program inserts into emp table.


CREATE OR REPLACE TYPE OBJ_EMP AS OBJECT (EMP_ID NUMBER , EMP_NAME VARCHAR2(100));

CREATE OR REPLACE TYPE TBL_EMP AS TABLE OF OBJ_EMP;

create table emp (
emp_id number,
emp_name varchar2(100)
);

alter table emp add constraint emp_pk primary key (emp_id);

CREATE OR REPLACE PROCEDURE INSERT_EMP( P_EMP TBL_EMP)
IS
v_obj_emp OBJ_EMP;
BEGIN

--insert into emp select * from table(p_emp);

IF P_EMP.COUNT > 0 THEN
FOR i IN 1 .. P_EMP.COUNT LOOP
v_obj_emp := P_EMP( i );

INSERT INTO emp ( emp_id,emp_name )
VALUES ( v_obj_emp.emp_id,v_obj_emp.emp_name);
END LOOP;
END IF;


END;
/


The INSERT_EMP procedure is invoked from anonymous block of plsql below. Here an employee object is intialized and table of employee type created. Employee type object t_emp is extend to create a row in the table object. The index of table object start with one which we assign to p_obj_emp object initially created. The t_emp table object is extended again and assigned to newly intialized p_obj_emp. Finally, INSERT_EMP procedure is invoked with t_emp object as parameter.


declare
p_obj_emp obj_emp := new obj_emp(1,'mahesh prajap');
t_emp tbl_emp := new tbl_emp();

begin
t_emp.extend();
t_emp(1):=p_obj_emp;

t_emp.extend();
p_obj_emp.emp_id:=2;
p_obj_emp.emp_name:='aarav prajap';
t_emp(2):=p_obj_emp;
INSERT_EMP(t_emp);
end;
/


The object programming concept in oracle can be evenly used with object oriented programming languages like java. List of objects can be passed directly to oracle procedure so that we can manipulate those passed objects in oracle as tables. There will be need and requirement in business logic that whole object be passed as tables and oracle plsql procedure takes control over the business procedures. The single object procedure call makes multiple call to procedure that makes more connection to oracle database and much of the time may be spent on such database connections.