100501 - non-Oracle Exception

Sachindra Kumar Meshram v · *nix forums Guru Wannabe Joined: 12 May 2005 Posts: 227

I encountered 100501 - non-Oracle Exception when i used raise
form_trigger_failure in a loop; I require the form to pause after
displaying the message i require.

--
Message posted via http://www.dbmonster.com

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

silvy sharma via DBMonster.com wrote:

SRISHIVA · *nix forums beginner Joined: 14 Jul 2006 Posts: 1

Problem Description:
====================

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

psoug · *nix forums Guru Joined: 15 May 2005 Posts: 3492

SRISHIVA wrote:

Google