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!
