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:
V_Item VARCHAR2 (61);
V_Item := :SYSTEM.CURSOR_ITEM;
IF One_Time_Timer.Get_Value = Const.ott_Query_in_EMP THEN
ELSIF One_Time_Timer.Get_Value = Const.ott_Something_Else
-- if more One-Time-Timer are needed,
-- create one for each Branch
create a Const-Package with some constants:
PACKAGE Const IS
gbl_One_Time_Timer CONSTANT VARCHAR2 (61) :=
ott_Query_in_EMP CONSTANT VARCHAR2 (30) :=
ott_Something_Else CONSTANT VARCHAR2 (30) :=
and a package for general functions:
PACKAGE One_Time_Timer IS
FUNCTION Get_Value RETURN VARCHAR2;
PROCEDURE Initialize (P_Event IN VARCHAR2);
PACKAGE BODY One_Time_Timer IS
FUNCTION Get_Value RETURN VARCHAR2 IS
Default_Value (NULL, Const.gbl_One_Time_Timer);
RETURN (NAME_IN (Const.gbl_One_Time_Timer));
PROCEDURE Initialize (P_Event IN VARCHAR2) IS
tm_name VARCHAR2 (30) := 'ONE_TIME_TIMER';
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);
the control-block (named "Filter") has e.g. two items: ENAME and SAL
create a WHEN-VALIDATE-ITEM on ENAME :
last step: the EMP-block needs a PRE-QUERY-trigger on block-level:
IF :Filter.ENAME IS NOT NULL THEN
:EMP.ENAME := :Filter.ENAME;
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.
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!