Re: Forms: SELECT returns 1 or 0

Anil (aarhemtu_at_cayley.uwaterloo.ca) wrote:
> In and using FORMS 4 with Oracle 7.
 

> In one of my blocks I am trying to create a trigger to load a phone
> number based upon the office in the block. So I created a "POST-QUERY"
> trigger as follows:
 

> SELECT phone_number
> INTO :my_block.phone_number
> FROM phone_table
> WHERE office = :my_block.office;

> This works great, except that some offices do NOT have phone numbers. So,
> the select state does not return anything. I would like the phone number
> item to remain empty in these cases yet I get an Oracle Forms error telling
> me that NO_DATA_FOUND.

Add an exception handler: (this must be in the manual, surely?) BEGIN
 <your select statement here>
EXECEPTION
WHEN NO_DATA_FOUND THEN
  NULL; -- do nothing
WHEN TOO_MANY_ROWS THEN
  message('Oops!');
  RAISE FORM_TRIGGER_FAILURE;
/* Add any thing else you want in here */ END;

--
  _________________________   ________________________________
 /  Tommy Wareing          \ /  Dying ain't much of a living, \
|  p0070621_at_brookes.ac.uk   X   When you're out and on the run,|
 \  0865-483389            / \  Dying ain't much of a living,  |
  ~~~~~~~~~~~~~~~~~~~~~~~~~   \ When you're young             /
                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~