OraFAQ Forum: Forms » Age condition trigger

Home » Developer & Programmer » Forms » Age condition trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Age condition trigger [message #501149]

Sat, 26 March 2011 08:24

snookian
Messages: 17
Registered: December 2010

Junior Member

Hi i have this trigger linked to a button, but when i try it an error comes up on oracle application server form saying "FRM-40735: trigger raised unhandled exception ORA-01403"

declare
	
		alert_button		number;
		age			date;
		ageless			number;
begin		
select date_of_birth
into age
from emp	
where :social_security.person_id = emp.person_id;
ageless := months_between(sysdate, age)/12;
				
if (ageless <65) then
		alert_button :=show_alert('ALERT_TOO_YOUNG');
		if 
			alert_button =alert_button1 then
			clear_record;
		end if;
		else go_item('SOCIAL_SECURITY.CATEGORY_ID');
	end if;
end;

Report message to a moderator

Re: Age condition trigger [message #501153 is a reply to message #501149]

Sat, 26 March 2011 09:07

snookian
Messages: 17
Registered: December 2010

Junior Member

where :social_security.person_id = emp.person_id;

should be

where :PERSON.PERSON_DOB = Person.Person_dob;

Someone please help!

Report message to a moderator

Re: Age condition trigger [message #501154 is a reply to message #501153]

Sat, 26 March 2011 09:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

01403, 00000, "no data found"
// *Cause: No data was found from the objects.
// *Action: There was no data from the objects which may be due to end of fetch.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Age condition trigger [message #501155 is a reply to message #501154]

Sat, 26 March 2011 09:23

snookian
Messages: 17
Registered: December 2010

Junior Member

sorry didn't relise i posted wrong!!!

Report message to a moderator

Re: Age condition trigger [message #501156 is a reply to message #501155]

Sat, 26 March 2011 09:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted code.

Report message to a moderator

Re: Age condition trigger [message #501158 is a reply to message #501156]

Sat, 26 March 2011 09:36

snookian
Messages: 17
Registered: December 2010

Junior Member

This for coursework so cant upload everything.
I have a table called person with the fields person_id, and Person_dob. In oracle server forms (online) when im entering these fields and then when i press a button i want it to flag and alert on oracle server forms, saying the user is under 17, or nothing if over. I believe the code is close to being right, but ovo just need a hint or pointing to something that can help my solve the problem.

Report message to a moderator

Re: Age condition trigger [message #501159 is a reply to message #501158]

Sat, 26 March 2011 09:53

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have a table called person
>from emp
HUH?

You can test the SQL independent of the form.

Report message to a moderator

Re: Age condition trigger [message #501160 is a reply to message #501159]

Sat, 26 March 2011 10:00

snookian
Messages: 17
Registered: December 2010

Junior Member

yeah sorry that was a mistake, meant to be from Person. tested with the right "from" and thats the error i get

Report message to a moderator

Re: Age condition trigger [message #501161 is a reply to message #501160]

Sat, 26 March 2011 10:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>yeah sorry that was a mistake, meant to be from Person. tested with the right "from" and thats the error i get
We only know what you post.
Sad

Report message to a moderator

Re: Age condition trigger [message #501162 is a reply to message #501161]

Sat, 26 March 2011 10:12

snookian
Messages: 17
Registered: December 2010

Junior Member

mistake!!!

Report message to a moderator

Re: Age condition trigger [message #501170 is a reply to message #501162]

Sat, 26 March 2011 14:53

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This query (from your first message):

select date_of_birth
into age
from emp	
where :social_security.person_id = emp.person_id;

didn't find any record in the EMP table where EMP.PERSON_ID is equal to data block's :SOCIAL_SECURITY.PERSON_ID value.

How to test? When you run the form, you see (on the screen) :SOCIAL_SECURITY.PERSON_ID value. Remember it. Now open SQL*Plus and run the same SELECT statement, but this time substitute data block's item name with the value you remembered a moment ago. Imagine that :SOCIAL_SECURITY.PERSON_ID was 12345. You'd run

select date_of_birth
into age
from emp	
where emp.person_id = 12345;

That query won't return anything, so Oracle told you that there was no data found.

Question is: what do you want to do now? The right way would be to handle that exception properly (in EXCEPTION section), alerting user that person with that ID doesn't exist. Something like this:

declare
  alert_button  number;
  age           date;
  ageless       number;
begin
  begin
    select date_of_birth
      into age
      from emp
      where :social_security.person_id = emp.person_id;
  exception
    when no_data_found then
      message('Employee does not exist');
      raise form_trigger_failure;
  end;

  ageless := months_between(sysdate, age)/12;

  if ageless < 65 then
     alert_button :=show_alert('ALERT_TOO_YOUNG');
     if alert_button = alert_button1 then
        clear_record;
     end if;
  else 
    go_item('SOCIAL_SECURITY.CATEGORY_ID');
  end if;
end;

While we're at it: note how my code is properly formatted, indented and easy to read. Try to format your code in your future messages.

Report message to a moderator

Previous Topic:	Setting a date 18 years in the future
Next Topic:	Stopping forms message Do you want to save the changes...?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 16:18:57 CDT 2024