How can I revert changes to a record in Oracle Forms 6i?

Question

I have a form running in Oracle Forms 6i which has tabular formatted rows that are being populated from a certain table in the database. One column has a [List_Of_Values] property enabled to allow the user to select among possible values.

Some values among the list can only be selected if the user has permission to do that, and I have created a [ WHEN-VALIDATE-ITEM ] trigger to check the permission after the value has been changed. The trigger raises a form_trigger_failure to prevent the user from saving the changes done.

The problem is that if the user gets notified about lack of permission to select the value, then there is no way for the user to know the previous (old) value to select it again, unless the form is cancelled which will cause his other (valid) changes to be lost too.

Here is the code I have written in the trigger

DECLARE
NEW_LOCATION  VARCHAR2(100);
BEGIN
NEW_LOCATION := :BLK_MAT_STG_PLACES_PILE.STG_LOC_ID;

    IF NEW_LOCATION LIKE 'OH01%' THEN
        IF NOT :GLOBAL.USER_ID LIKE 'Admin%' THEN
            MESSAGEBOX('You are not authorized to select this value');

            /* What can I write to load the old value to this item? */ 
            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END IF;
END;

I have tried ROLLBACK but that did not revert the old value to the form. I tried SYNCHRONIZE as well, but that had no effect. Is there any option other than going through the database table again to pull out the value?

Answer 1

BEGIN    
    IF NEW_LOCATION LIKE 'OH01%' THEN
        IF NOT :GLOBAL.USER_ID LIKE 'Admin%' THEN
            MESSAGEBOX('You are not authorized to select this value');

            /* Return it to the original value that was fetched from database */ 
            :BLK_MAT_STG_PLACES_PILE.STG_LOC_ID :=
              get_item_property('BLK_MAT_STG_PLACES_PILE.STG_LOC_ID'
                               ,DATABASE_VALUE);

            RAISE FORM_TRIGGER_FAILURE;
        END IF;
    END IF;
END;