Returning Array from Oracle DB

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.

Read Full Post »

Hierarchical Queries – Connect By Prior

Recursive queries are something extensively used when hierarchical data comes in picture. A query is referred to as a recursive query when it references itself, in order to retrieve hierarchical data. So one thing that comes into mind – what is this hierarchical data and how recursive queries help us.  Also is there any alternative to recursive queries.

First let get some acquaintance with hierarchical data.

Read Full Post »

Choice of the right DBMS – DBA’s nightmare

In today’s world of data and application informatics, application ranging from large business intensive to small desktop stuff needs a database. The database used, either can be in the form of a single binary or text file (which just stores the info) to a sophisticated DBMS (with most advanced features). Thus acting as a lifeline for applications, a suitable and best choice of database can be critical.

The worst DBA’s nightmare is the choice of the right DBMS for a particular application. So one wonders – what are the areas one should consider before making the choice. Well based on some findings and research, here are some things to be looked up in DBMS:

• Performance
• Data Security
• Replication capabilities
• Recovery mechanism

So let’s drill down in some of the popular DBMS in market. We will use Oracle, Sql Server 2008 and MySql for our comparison.

Read Full Post »