Update
You can streamline the entire experience with following simplified WHEN-TIMER-EXPIRED code
Begin
if GET_APPLICATION_PROPERTY(T IMER_NAME) = ‘MY_TIMER’ then
GO_BLOCK(‘YOUR_BLOCK_NAME’); Execute_Query(NO_VALIDATE);
end if;
and change your WHEN-VALIDATE-ITEM trigger coding as following
DECLARE
t_id TIMER;
Begin
/* Do all your validations here, raise form failure trigger etc until the entered data is satisfying the requirements*/
t_id := CREATE_TIMER('MY_TIMER',1,NO_REPEAT);
End;
This will create & expire the timer as soon as the validations completed and WHEN-TIMER-EXPIRED trigger @ form level will check the expired timer name and process the instructions..
Note: Infrastructure (Windows 7 64bit professional, Oracle developer 6i 32bit (6.0.8.27.0), Database 11g Enterprise
Source/Cause for this thread: click here
If you are Oracle developer, the most perplexing requirement would be populating a dataset within a second block once after an item validated using restricted trigger ‘WHEN-VALIDATE-ITEM’. By default ‘WHEN-VALIDATE-ITEM’ doesn’t allow calling restricted procedure calls like ‘GO_ITEM’, ‘GO_BLOCK’ and many others.
However, there are certain workarounds. A developer may use WHEN-TIMER-EXPIRED trigger to navigate away from the items which was just validated using ‘WHEN-VALIDATE-ITEM’ trigger.
Alter your WHEN-VALIDATE-ITEM as following:
DECLARE
t_id TIMER;
Begin
/* Do all your validations here, raise form failure trigger etc until the entered data is satisfying the requirements*/
t_id := CREATE_TIMER('QRY_BLOCK',1,REPEAT);
End;
Add the following to your WHEN-TIMER-EXPIRED trigger @ form level.
Declare
t_id TIMER;
Begin
t_id := find_timer('QRY_BLOCK');
IF NOT ID_NULL(t_id) then
GO_BLOCK('YOUR_BLOCK_NAME');
Execute_Query(NO_VALIDATE); -- Example
DELETE_TIMER('QRY_BLOCK'); -- Deleting the timer as we don't require it any further until the item fires WHEN-VALIDATE-ITEM trigger once again.
END IF;
/* Check whether the timer is still alive
for testing purpose only
*/
t_id := find_timer('QRY_BLOCK');
IF ID_NULL(t_id) then
message('TIMER ALREADY DELETED');
end if;
End;
Hope this example is useful for budding Oracle developers.
For Windows7bugs,
Admin
Actual scripting by OTN Guru Craig is as following and he suggests the code runs perfectly under certified environments.
” The best place would be the Item When-Validate-Item (WVI) trigger but you will need to create a timer in your WVI code in or der to execute the Restricted Built-in since the WVI trigger only allows SQL and Unrestricted built-ins. You will also need to create a Form level When-Timer-Expired (WTE) trigger.”
For example:
/* Add this code to your WVI trigger */
DECLARE
t_id TIMER;
BEGIN
/* Perform your validate here */
t_id := Create_Timer('QRY_BLOCK',1,NO_REPEAT);
END;
/* Sample code for your WTE trigger */
DECLARE
t_id TIMER;
BEGIN
t_id := Find_Timer('QRY_BLOCK');
IF NOT ID_NULL(t_id) THEN
-- Timer Found
GO_BLOCK('YOUR_BLOCK_NAME');
Execute_Query;
END IF;
END;
It is working fine Rajesh….Thanks
Selvan
Thank you so much :)
used exact code in the timer and and when validate item and my commit in when timer expired is not wotking.can u plz suggest
I am glad that read that you fixed the issues, can you please explain why you have to commit a transaction within a validate routine??
the requirement I have is to ‘commit’ the record after when validate item.