DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Insert single space into NOT NULL field in FORMS

  1. #1
    Join Date
    Apr 2001
    Posts
    127

    Question

    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?

  2. #2
    Join Date
    Apr 2001
    Posts
    127
    Just to mention that I create the Pre-Update trigger at the item-level.

  3. #3
    Join Date
    Jun 2001
    Location
    Luxemburg
    Posts
    9
    Hi,

    I have never try this but
    BEGIN
    :block_name.column_name := NVL(:block_name.column_name , chr(XX));
    END;

    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


  4. #4
    Join Date
    Apr 2001
    Posts
    127
    I try it but still...

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

  5. #5
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54
    Move the code to database level trigger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width