Re: ORA 01839: Forms 5.0 Y2K bug

A copy of this was sent to kaiserrj_at_my-deja.com (if that email address didn't require changing) On Mon, 14 Jun 1999 13:34:36 GMT, you wrote:

>Hey all,
>
>Can't find anything about this in newsgroups, Metalink, or Technet.
>
>GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE) causes ORA-01839
> ("date not valid for month specified") errors when the returned
> date is FEB 29, 2000.
>NOTE: it works fine for any other date (that I've tested).
>

i don't believe this is a bug at all. get_item_property() returns a VARCHAR. When you ask for a date field, it is "to_char()'ed". The default date mask is probably being used -- dd-mon-yy. hence you are getting the string 29-FEB-00 which when you implicity to_date() it is being interpreted as Feb 29'th, 1900 which is not valid.

Beware of conversions of DATES to STRINGS and vice versa -- they'll get you every time.

>The failure occurs when accessing FEB 29, 2000 in an assignment to
> a date variable:
> dt := GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE);
>
>The failure occurs when accessing FEB 29, 2000 in an argument to
> a date function:
> dt := TO_DATE(GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE));
> str := TO_CHAR(GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE));
>
>The failure does not occur inside a simple MESSAGE statement:
> message(GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE));
>
>This method was used in an ON_UPDATE trigger to compare the original
> date value (prior to any updates) to the current one.
> IF :block.item = GET_ITEM_PROPERTY(:block.item, DATABASE_VALUE) THEN
>
>Hope this may save a few people some time, next year, right around
>the end of February...
>
>Rich
>
>Claim: The company that owns this intellectual information has okay'd
> my posting of it. The information itself and any opinions stated
> here are created by myself and are not warrented or verified by any
> other person or organization. Use this information at your own risk.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

[Quoted] Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation