| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Form Properties. Range High Value
At 12:36 PM 12/29/95 +0800, you wrote:
>Hi Paul & John !
>
>Thanks 4 your replies.
>
>The problem is solved using the below validation routine in
>POST-CHANGE trigger.
>
>declare
> diff number;
> testdate number(8) := to_number(to_char(testdate,'YYYYMMDD'));
> dbdate number(8) :=
> to_number(to_char(database_date,'YYYYMMDD'));
>begin
> diff := testdate - dbdate;
> if diff >= 0 then
> display error msg and raise form trigger .....
> end if;
>end;
>
>PS : Paul, in reply to your earlier mail, I need to use the database date
>'cause sometimes if the CMOS battery fails or what not, the operating
>system date which is sysdate will not be accurate. What I did is to
>declare another field of date type date and default value =
>$$DBDATE$$.
>
>Paul Dorsey wrote in his 1st reply:
>>
>> You have to trick it to do date comparisons. Try this in your trigger:
>> declare
>> dif number;
>> begin
>> dif := :testdate - sysdate;
>> if dif < 0 then
>> bell;
>> message ('big');
>> else
>> message ('small');
>> end if;
>>end;
>
>It does not work.
>Let me reiterate my aim : testdate must be earlier than current date.
>NOTE THAT the field of data type date has the date portion as well
>as the time portion.
>
>Using Paul's method
>
>declare
> dif number;
>begin
> dif := :testdate - sysdate;
> message('The difference='||to_char(dif));
> if dif >= 0 then
> message ('Error Message ');
> raise form_trigger_failure;
> end if;
>end;
>
>
>Assuming Sysdate = 29-Dec-1995
>
>Test Case 1
>Test Date = 28-Dec-1995 works
>
>Test Case 2
>Test Date = 29-Dec-1995 fails
>'cause dif = -0.4062.... and not = 0 due to the time portion.
>
>Test Case 3
>Test Date = 30-Dec-1995 works
>
>Paul Dorsey wrote in his 2nd reply:
>> Wait a minute. I just tried using when_validate_item with your
>> origional code and it works fine. I hate to suggest this, but did
>> you declare your screen item to be of type "DATE"?
>
>Yes, I did declare it to be of type "DATE' & it still does not work as
>explain above.
>
>John Thomas wrote :
>> Have you named the trigger ON_VALIDATE_FIELD? Forms would
>> consider this a user-named trigger & would not execute it.
>>
>> Just some guesses...
>
>Thanks John. It is not a user-defined trigger.
>
>ChorLing
>chancl_at_nievax.nie.ac.sg
>
Hi
Looking at your problem where you are finding problem with time portion of date field you can try using function trunc, this will remove time portion of date field.
testdate := trunc(testdate);
Hope this helps
Email: singhp_at_bactc.com
Voice: 415-827-5868
Fax: 415-266-6324
 |
 |