Re: 100501 - non-Oracle Exception

From: SRISHIVA <shivav75_at_yahoo.com>
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:    

1. 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