Tuesday, April 19, 2011

The curse of the cursor

For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT INTO.

The code resembled this:

procedure do_something (p_empno in number)
l_empno number;
l_boss_ind varchar2(1) := 'N';
cursor c_emp (cp_empno number) is
select job
from emp
where empno = cp_empno;
for rec in c_emp (l_empno) loop
if rec.job = 'MANAGER' then
l_boss_ind = 'Y';
end if;
end loop;
insert into some_table (empno, boss_ind) values (p_empno, l_boss_ind);

Sometimes the BOSS_IND column wasn't being set correctly. The code was a lot more complex than this in reality. You can probably easily and quickly spot the error above, but in the real procedure there was a lot of other code that could also set the BOSS_IND under different circumstances, and it was, as I said, about an hour before I spotted the problem which is: variable l_empno was not set when the cursor was opened (it got set further down), so the cursor returned no rows and the code inside the loop was never executed.

If the developer had coded this using SELECT INTO, a very unexpected NO_DATA_FOUND exception would have been raised as soon as he ran this for the first time, and the bug would never have reached system testing (as it had). Assuming of course he didn't decide that SELECT INTO had "caused" the bug, and didn't "fix" it by changing to use a cursor...

Monday, April 04, 2011

Apex MP3 Player item plug-in

I have developed a plug-in item type that renders an MP3 player to play a specified audio file using the free Premiumbeat Single Track Flash MP3 Player. The file may be specified via a URL of as the ID of a file stored in APEX_APPLICATION_FILES. There is a demo of this available here on apex.oracle.com

The plug-in has the following settings available:

They are mostly fairly self-explanatory (and documented via Help). They can generally be left to their default values. The Media Title attribute specifies the title displayed in the player. This can be set to an item reference e.g. &P2_TRACK_NAME. If not specified the URL of the file is displayed.

The value of the item can be either of the following:

  • a number, in which case it is assumed to be the ID of an APEX_APPLICATION_FILES row containing the MP3 data

  • any URL pointing to an MP3 file

This should shortly be available to download from apex-plugin.com.