One Time Timer

Many times I want to code a go_item, go_block or execute_query while validating an item. But restricted functions can't be used in many triggers. So we need a workaround.

And here comes my "One Time Timer" :

Example: I have a non-basetable control-block with some items. Below this block is a multi-record block based on EMP. The control-block should be used as filter on the EMP-block.

The user wish to enter filter-criteria in the master-block and when navigating to the next item, they automatically want a new query-result in the block EMP. This is impossible with standard validation-triggers, because the navigation has to go into a block and execute a query, while validating an item.

Solution: create a form-level WHEN-TIMER-EXPIRED:

DECLARE
 V_Item   VARCHAR2 (61);
BEGIN
 V_Item := :SYSTEM.CURSOR_ITEM;

 IF One_Time_Timer.Get_Value = Const.ott_Query_in_EMP THEN
   Go_Block ('EMP');
   Execute_Query;
   Go_Item (V_Item);
 ELSIF One_Time_Timer.Get_Value = Const.ott_Something_Else
 THEN
   -- if more One-Time-Timer are needed,
   -- create one for each Branch
   NULL;
 END IF;
END;

create a Const-Package with some constants:

PACKAGE Const IS

-- Globals
 gbl_One_Time_Timer CONSTANT VARCHAR2 (61) :=
   upper ('global.One_Time_Timer');

-- One-Time-Timer
 ott_Query_in_EMP   CONSTANT VARCHAR2 (30) :=
   'Filter EMP-Block';
 ott_Something_Else CONSTANT VARCHAR2 (30) :=
   'Something else';

END;

and a package for general functions:

PACKAGE One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2;
 PROCEDURE Initialize (P_Event IN VARCHAR2);
END;

PACKAGE BODY One_Time_Timer IS
 FUNCTION Get_Value RETURN VARCHAR2 IS
 BEGIN
   Default_Value (NULL, Const.gbl_One_Time_Timer);
   RETURN (NAME_IN (Const.gbl_One_Time_Timer));
 END;

 PROCEDURE Initialize (P_Event IN VARCHAR2) IS
   tm_id   timer;
   tm_name VARCHAR2 (30) := 'ONE_TIME_TIMER';
 BEGIN
   tm_id := Find_Timer (tm_name);
   IF ID_Null (tm_id) THEN
     tm_id := Create_Timer (tm_name, 10, NO_REPEAT);
     COPY (p_Event, Const.gbl_One_Time_Timer);
   END IF;
 END;
END One_Time_Timer;

the control-block (named "Filter") has e.g. two items: ENAME and SAL

create a WHEN-VALIDATE-ITEM on ENAME :

BEGIN
 One_Time_Timer.Initialize (Const.ott_Query_in_EMP);
END;

last step: the EMP-block needs a PRE-QUERY-trigger on block-level:

BEGIN
 IF :Filter.ENAME IS NOT NULL THEN
   :EMP.ENAME := :Filter.ENAME;
 END IF;
END;

what happens?
After changing the value of ENAME in the FILTER-block the WHEN-VALIDATE-ITEM fires. He initializes the One-Time-Timer. The "global.One_Time_Timer" get the value of Const.ott_Query_in_EMP (which is "Filter EMP-Block"). After that a timer is created which fires 10 ms later.

10ms later:
The WHEN-TIMER-EXPIRED fires and does an execute_query in the EMP-block and returns afterwards back to the original item. In the EMP-block starts the PRE-QUERY and the data of the EMP-block gets filtered through ":EMP.ENAME := :Filter.ENAME"

that's the whole story. Try it and have fun!