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:
:block_name.column_name := NVL(:block_name.column_name , ' ');
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:
:block_name.column_name := NVL(:block_name.column_name , ' TEXT');
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?
Just to mention that I create the Pre-Update trigger at the item-level.
I have never try this but
:block_name.column_name := NVL(:block_name.column_name , chr(XX));
with XX the ASCII code of the space character (sorry i don't remember if it is 32 or 20)
Let me know if it works
I try it but still...
:block_name.column_name := NVL(:block_name.column_name , chr(32));
Move the code to database level trigger.