Sunday, February 1, 2009

Select column into variable

In pl/sql world, there is one easily prone error, which is the select column into variable. This is how variable is assigned with value from the column result. Example:

Define
myid number; --my variable

begin
select employeeid into myid from employee where name = 'ivan';
end;

A simple example to store a number type employeeid into the myid variable. This is a very common error that programmer make in the development because there always a assumption that there are no more than a row return. Even the above example, the table only store one record and that makes good assumption right? Wrong.

The problem comes when the table grow without the programmer know about it and there are another employee named 'ivan' creep into the table. Well, oracle will throw an error stated that the result return more row than expected.

ORA-01422: exact fetch returns more than requested number of rows

If you're lucky, the error show in your working hour. If you're unlucky, the error show on your weekend while you're enjoying your favorite tv show and your boss suddenly want you to get back to office. Gee...

Perhaps programmer needs to do a little thinking on the table (database type table) before write such line of code. Below is some of the ideas i can think of:
  1. The query where clause is based on primary key or unique index. Since primary and unique index will not have duplicates, therefore this way we can assure there always only a single record return.
  2. Use a for loop to assign variable. 'For loop' is not mean to assign variable but it still do the trick. The variable keep being assign if more than a row returned. However, the value might not be the one we're after.
  3. Use an exception to catch the error. What comes after exception has been catch? Which value is the one we want? Perhaps a default value? This idea will not fully resolve our problem but may get away with it.
In conclusion, stick to the first idea. This will always get a single result for the variable. Else, be prepare to get a earlier wake up call from boss to debug the system. :P