December 20, 2006

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.

December 15, 2006

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

December 05, 2006

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;