Hi, All:

I have a question about how to allow empty field for a NOT NULL item in FORMS.
The form I am working on has a item based on a NOT NULL column of a table, based on business requirement, sometimes I need to allow users to leave this field empty, but since this column is NOT NULL, user can't commit the change. I am thinking of creating a Pre-Update trigger and using the NVL function so that if the user leave this field empty, I will insert a single space into that column.

I create PRE-UPDATE trigger:

BEGIN
:block_name.column_name := NVL(:block_name.column_name , ' ');
END;

The compilation is OK, but when I run the form, if I leave that item field empty, it doesn't allow me to go to other fields; if I click the SAVE button, it pop out the error window:

FRM-40202: Field must be entered

Then in the Property Palette, I change the "Required" property from "YES" to "NO", so now I can move to other fields; but I still can't save the change if I leave that field empty, I get this error:

FRM-40509: Oracle error: unable to UPDATE record.

Seems that FORM still treate that item as a NULL value because if I change the trigger to:

BEGIN
:block_name.column_name := NVL(:block_name.column_name , ' TEXT');
END;

It works, but our requirement is to put single space there :(

But if I use SQL*PLUS and issue this command:

update table_name set column_name = ' ' where ...;

I can commit it!!! So what's going on?! Is this a BUG of FORM?