Many a times, we come across situations where there is a need to return an array of objects from Oracle DB instead of rows of data.
Traditionally, in good old days whenever there was a need of array of objects (e.g.: Names of Employees from a particular department), we use to use a simple query to retrieve all DB rows with a particular department criteria (select Empname from Employee where Dept = ‘admin’). While the task of traversing all DB result rows and formation of array was done by backend code. Now, imagine repeating the procedure for hundreds of department, it will definitely burden the backend logic.
So the question arises, how Oracle DB can directly return an array of objects (data)? Is there an Datatype which can support this? Well the answer is hidden in effective use of both Varray and Oracle function.
Let’s have an example to get a better insight about returning an array. In the tutorial mentioned, we will use two mostly used traditional tables, ‘Employee’ and ‘Department’ and return list of all Employees present in particular department:
Create ‘Department’ and ‘Employee’ table:
- Structure of Department table:
Create Table Department (DeptNo number primary key, DeptName varchar(20));
- Structure of Employee table:
Create Table Employee (EmpNo number primary key, EmpName varchar(20), DeptNo number foreign key references Department(DeptNo)) ;
Insert some sample data in both the tables:
Insert into Department Values (1, ‘Admin’);
Insert into Employee Values (1, ‘John’, 1);
Insert into Employee Values (2, ‘Dorcy’, 1);
Insert into Employee Values (3, ‘Jacky’, 1);
Insert into Department Values (2, ‘BackOffice’);
Insert into Employee Values (4, ‘Mishel’, 2);
Insert into Employee Values (5, ‘Admen’, 2);
Create a user type (object) of Varray:
Create or Replace Type EmpNameArray is Varray(20) of Varchar(30);
The command create array of varchar objects of initial size 20. The array is expandable
Create a function to return Varray:
Here we will create a function which traverses through all employees of department ‘Admin’
In the above code snippet, we create a function name ‘GetEmpNames’ which will return array of all employees with particular department number.
Line 3, create an object of ‘EmpNameArray’ which is basically an Varray of varchars
Line 7, emp_data.Extend – extends the size of Varray if we have to insert employee names more than its initial size of 20
Line 8, emp_data (emp_data.count) – appends new object to the exciting Varray object. So the lastest object will be the last object in the array
View the Object:
The value returned from the function above will be an Object which can be directly used in our backend code. But in order to verify the return data in Oracle use the query given:
Select employees.column_value ‘EmployeeNames’ from Table (GetEmpNames(1)) employees;
Where GetEmpNames is the call to ‘GetEmpNames’ function
So using this tutorial, we got insights of how to effectively use Oracle Varray Datatype with functions to directly return an array of objects. Let me know if this article for useful.
Leave a comment