Re: 100501 - non-Oracle Exception
Date: Fri, 14 Jul 2006 11:42:42 -0400
Message-ID: <bf049ad5f98be5e3bbe77423ebb49e53_at_localhost.talkaboutdatabases.com>
Problem Description:
[Quoted] In a multi-record block:
How can I validate data at the record level, and then depending on the
field
that is invalid, navigate to that invalid field in the invalid record
allowing re-entry of data?
Problem Explanation:
The GO_ITEM built-in cannot be used within the When-Validate-Record trigger to position the user in the invalid item.
[ Search Words: not valid failed validation cursor WHEN-TIMER-EXPIRED
WHEN-NEW-RECORD-INSTANCE navigation return focus data reentry multi record SYSTEM.CURSOR_RECORD GO_ITEM GO_RECORD CREATE_TIMER ]
Solution Description:
Create the following triggers with the trigger text given below:
WHEN-TIMER-EXPIRED (Form level) WHEN-NEW-RECORD-INSTANCE (Multi-record block level) WHEN-VALIDATE-RECORD (Multi-record block level)
When an invalid item is encountered, a timer is created. When this timer
is
executed, the process does a GO_RECORD and a GO_ITEM to position the
cursor in
the invalid field.
The example below assumes the block has been created on the scott/tiger
DEPT
table, containing items DEPTNO and LOC.
WHEN-TIMER-EXPIRED
- this trigger when fired will cause navigation to the invalid
- record and the invalid field GO_RECORD(:GLOBAL.inv_rec); GO_ITEM(:GLOBAL.inv_fld);
WHEN-NEW-RECORD-INSTANCE
- save the record number of the record just entered :GLOBAL.curr_rec := :SYSTEM.CURSOR_RECORD;
WHEN-VALIDATE-RECORD
- clear invalid field value and save the invalid record no.
- if field is invalid, set invalid field name, set timer
- display message indicating which field is invalid
DECLARE timer_id Timer; BEGIN :GLOBAL.inv_field := ''; :GLOBAL.inv_rec := :global.curr_rec; IF :dept.deptno > 50 THEN :GLOBAL.inv_fld := 'DEPTNO'; timer_id := CREATE_TIMER('GOTIMER',60, NO_REPEAT); Message('invalid department number'); END IF; IF :DEPT.LOC = 'XYZ' THEN :GLOBAL.inv_fld := 'LOC'; timer_id := CREATE_TIMER('GOTIMER',60, NO_REPEAT); Message('invalid location'); END IF; END;
Solution Explanation:
Considerations:
- You can use a WHEN-VALIDATE-RECORD trigger to validate the record. This will fire when the user tries to navigate out the record where information has been changed. If the any of the fields are invalid, and a form_trigger_failure is raised, then the cursor will stay in the record.
The issue with this solution is that a GO_ITEM cannot be used within the
When-Validate-Record trigger, to position the user at the invalid item.
GO_ITEM is a restricted built-in, and the WHEN-VALIDATE-RECORD does not
allow restricted built-ins.
2. After the validation is done and one of the items is invalid, Forms must
go back to the invalid record. Then Forms must do a GO_ITEM to go to the invalid item.
The issue here is how to determine which record the user just navigated out
of (since with mouse capability it is not necessarily the previous record).
Additional Information:
Oracle Documentation:
Oracle Forms 4.X Reference Manual, Volume 1 Chapter 2, Triggers
WHEN-TIMER-EXPIRED WHEN-NEW-RECORD-INSTANCE WHEN-VALIDATE-RECORD
Chapter 3, Built-in Subprograms
CREATE_TIMER
GO_ITEM
GO_RECORD
Chapter 4, System Variables
SYSTEM.CURSOR_RECORD Received on Fri Jul 14 2006 - 17:42:42 CEST