Oracle WHEN-VALIDATE-ITEM trigger go_item, go_block navigation using WHEN-TIMER-EXPIRED trigger

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;

About these ads

One Response to Oracle WHEN-VALIDATE-ITEM trigger go_item, go_block navigation using WHEN-TIMER-EXPIRED trigger

  1. selvan says:

    It is working fine Rajesh….Thanks
    Selvan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 53 other followers

%d bloggers like this: