January 20, 2007

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!

8 comments:

Mohamed said...

Dear Sir,

i have tried your ONE-TIME-TIMER
but when i reach the statement GO_BLOCK('myblock');

i receive the message
'illegal restricted procedure go_block '

APPRECIATING YOUR HELP

Gerd Volberg said...

The go_block is in the trigger "WHEN-TIMER-EXPIRED". Legal commands for that trigger are: unrestricted built-ins, restricted built-ins. So please check your code

Mohamed said...

i changed only the trigger when-timer-expired

as follows

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 ('HR_EVAL_ATTENDED');
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;

Gerd Volberg said...

please debug the code and set a breakpoint in the first line of the when-timer-expired. Check, if the error occurs, when the go_block () is executed.

Gerd

Mohamed said...

Dear Sir,
thanks a lot for your quick reply
i appreciate that too much.

i did what you said ; and after i reach go_block() i receive the message.

i will tell you exactly what i need

1. i have a master DB block for some emp data and i need to show in detail control block the emp courses attended
1. so i made a when-validate-item trigger for the field emp_code and i tried your code

Gerd Volberg said...

ok, try this: Create a completly new form from scratch and do the steps from my post. I bet it will work. And after that you have to look, which things you do different in your form, where you get the error.
Gerd

Piyush Chaudhari said...

can we achieve this in custom.pll in oracle forms.... how?

Gerd Volberg said...

you can extract parts of the code into a custom.pll.

But typically the code is used in each form in a different way, so that it isn't useful to write it into a PL/SQL-Library

Gerd