Undo

Retrieving data from the database and changing the data is really easy. But what, if the user changes data and want to do an UNDO?

Doing a new query is the easiest way. The limitations are:

- in a multi-record-block you have to position in the correct record after the query
- if the query was executed via ENTER-QUERY mode you can't jump to the old record because the query-result has changed.

So you have to use a new technique.

The solution is this function. All database-items get their old values back:

PROCEDURE Undo IS
  V_Block  VARCHAR2 (30) := :SYSTEM.CURSOR_BLOCK;
  V_Field  VARCHAR2 (61);
  V_Item   VARCHAR2 (61);
BEGIN
  Validate (Item_Scope);
  IF :SYSTEM.RECORD_STATUS = 'CHANGED' THEN
    V_Field := Get_Block_Property (V_Block, FIRST_ITEM);
    V_Item := V_Block || '.' || V_Field;
    WHILE V_Field IS NOT NULL
    LOOP
      IF Get_Item_Property (V_Item, ITEM_TYPE) 
         IN ('DISPLAY ITEM', 'CHECKBOX', 'LIST', 
             'RADIO GROUP',  'TEXT ITEM')
      AND Get_Item_Property (V_Item, BASE_TABLE) = 'TRUE' 
      THEN
        COPY (Get_Item_Property (V_Item, DATABASE_VALUE), 
              V_Item);
      END IF;
      V_Field := Get_Item_Property (V_Item, NextItem);
      V_Item := V_Block || '.' || V_Field;
    END LOOP;
  END IF;
END;

Best practice is to start this undo-procedure from a menu (e.g. EDIT - UNDO) or handle it through a shortcut.

Equal and UnEqual

Sometimes you have to check the Equality of two variables.

Writing "IF A = B THEN" is not the solution for all cases. If one variable is NULL the whole statement is NULL and NULL becomes FALSE in an IF-Statement. So you have to work with a different technique:

FUNCTION Equal (P_String1  IN VARCHAR2,
                P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 = P_String2
  OR (P_String1 IS NULL AND P_String2 IS NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

FUNCTION UnEqual (P_String1  IN VARCHAR2,
                  P_String2  IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
  IF P_String1 != P_String2
  OR (    P_String1 IS     NULL
      AND P_String2 IS NOT NULL)
  OR (    P_String1 IS NOT NULL
      AND P_String2 IS     NULL) THEN
    RETURN (TRUE);
  ELSE
    RETURN (FALSE);
  END IF;
END;

Now you can easily use Equal and UnEqual

IF UnEqual (Var1, Var2) THEN
  -- do something
ELSE
  -- do something different
END IF;

try it

ON-ERROR and ON-MESSAGE-trigger

Many developer have problems with messages which popup in forms, for example "FRM-40401: No changes to save".

Then they look for workarounds and one of the easiest is manipulating the :system.message_level:

KEY-COMMIT - trigger on form-level (quick and dirty)

BEGIN
  :System.Message_Level := 25;
  COMMIT;
  :System.Message_Level := 5;
END;

or KEY-COMMIT - trigger

DECLARE
 V_Message_Level NUMBER;
BEGIN
 V_Message_Level := :System.Message_Level;
 :System.Message_Level := 25;
 COMMIT;
 :System.Message_Level := V_Message_Level;
END;

these are not best practices. Because you have to write those codes in hundreds of procedures and tons of code.

Filtering the errors and messages is the key to have a powerful message-handling. Here is an easy procedure which shows you the technique:

ON-ERROR - trigger on form-level

DECLARE
  V_Error_Code       NUMBER;
  V_Error_Text       VARCHAR2 (2000);
  V_DBMS_Error_Code  NUMBER;
  V_DBMS_Error_Text  VARCHAR2 (2000);
BEGIN
  V_Error_Code      := Error_Code;
  V_Error_Text      := Error_Text;
 V_DBMS_Error_Code := DBMS_Error_Code;
  V_DBMS_Error_Text := DBMS_Error_Text;
 
  IF V_Error_Code IN (40401, 40405) THEN
    /*
    || 40401, 40405 - no changes to save / apply  get filtered
    */
    NULL;
  ELSIF V_Error_Code IN (-1034, -3114) THEN
    /*
    || -1034, -3114 - not connected to database
    */
    Message ('Not connect to database, exiting Form');
    Exit_Form (no_validate);
  ELSIF V_Error_Code IN (40508, 40735)
  AND   V_DBMS_Error_Code BETWEEN -20999 AND -20000 THEN
    /*
    || -20000 errors are raised by RAISE_APPLICATION_ERROR 
    || They are handled in a different way
    */
    Show_and_Log_DB_Error (V_DBMS_Error_Text);
  ELSE
    /*
    || All other errors went into Show_and_Log_Error, where they
    || get inspected, analyzed and logged.
    */
    Show_and_Log_Error (V_Error_Code);
  END IF;
END;