Old Buggy Details

I was reviewing an issue involving a record modification form within a PL/SQL web application yesterday. This modification form is basic...it displays the data which already exists within a database record and allows an end-user to modify that data within different text fields on the form.

The issue was that some of these text fields were not updating as expected. After some initial review, I saw that the records having this issue had been submitted without any data in the fields that were not updating. Furthermore, I checked the database and all varchar2 fields which were not updating as expected had NULL values within them. Ahh...the old NULL value comparison issue!!!

As it turns out, upon submitting a modified record, the PL/SQL application checks each field within the database to see if it's contents match the previously submitted data. If not, then the field and new content are added to the update statement so that the resulting modifications are submitted successfully. However, the comparison being used was not allowing for NULL values to be compared. The result was a false positive result which was prohibiting the data to be updated.

For example, the data is compared such as:

if cursor_rec.old_value != incoming_new_value then
update_text := update_text || ' my_field = ''' || upper(incoming_new_value)
|| ''';
end if;

The issue is that the above IF statement is never true if the varchar2 value contained within the incoming_new_value variable is NULL. You must always compare a varchar2 against a non-null value for obtaining a valid result in such cases. Therefore, the use of the Oracle nvl function comes into play.

The updated code which repairs the issue simply replaces a NULL value with a character so that comparisons will function as expected.

if cursor_rec.old_value != nvl(incoming_new_value, '*') then
update_text := update_text || ' my_field = ''' || upper(incoming_new_value)
|| ''';
end if;

An oldie but goodie to remember!!!