SQL*Forms V3 bug

From: <pihlab_at_hhcs.gov.au>
Date: 11 Feb 93 14:16:12 +1100
Message-ID: <1993Feb11.141612.619_at_hhcs.gov.au>

SQL Bug found.

SQL*Forms field edit masks operate differently to SQL*Plus.

CASE #1   SELECT TO_CHAR(TO_DATE('01:01:01 16-JAN-93',

                         'HH24:MI:SS DD-MON-YYYY'),


                 'HH24:MI:SS DD-MON-YYYY')

    FROM DUAL;   Gives '01:01:01 16-JAN-0093'

CASE #2   SELECT TO_CHAR(TO_DATE('01:01:01 16-JAN-3',

                         'HH24:MI:SS DD-MON-YYYY'),


                 'HH24:MI:SS DD-MON-YYYY')

    FROM DUAL;   Gives '01:01:01 16-JAN-0003'

CASE #3   SELECT TO_CHAR(TO_DATE('01:01:01 16-JAN-993',

                         'HH24:MI:SS DD-MON-YYYY'),


                 'HH24:MI:SS DD-MON-YYYY')

    FROM DUAL;   Gives '01:01:01 16-JAN-0993'

The above all work consistently for SQL*PLus.

However, when you create similar edit masks for SQL*Forms you get

CASE #1   Works as for SQL*Plus but prior to Forms 3.0.16.10 it would actually   store '01:01:01 16-JAN-1993' in the database but display   '01:01:01 16-JAN-0093'.   We have a very large system that has been using the pre-3.0.16.10 "bug"   as a way allowing the users to enter current dates quickly and because   our users don't have resources to finance the changes necessary to get   around this "bug fix" it looks like we will be stuck on our current   release (forget about Oracle V7 too) for quite some time. Ho hum, that   will teach them to use undocumented features.

CASE #2 & #3   Fail with a message describing the valid format mask with 'YYYY'.   Is this a bug?

There are 3 possible ways that the 'YYYY' format mask should work

1. Imbed leading zeroes as it currently does in SQL*Plus, or
2. Imbed the current year's corresponding digit as in '3' becomes '1993', '99' becomes '1999', '399' becomes '1399', or
3. Flag it as an error if 4 digits aren't supplied.

I believe that option (a) is an incorrect translation for a DATE and that the correct operation should be either (b) or (c) with my preference being for (c) otherwise why would they give format masks in Oracle V7 of 'Y', 'YY', 'YYY', and 'YYYY'.

Apart from the bugs in SQL*Forms, is there a reason why Oracle have chosen option (a)? Is it to conform with ANSI standards?    

Would somebody from Oracle care to comment on this bug...

-- 

Bruce...        pihlab_at_hhcs.gov.au

"If you swallow a live frog first thing in the morning ...
 Nothing worse will happen to either of you for the rest of the day."

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

Received on Thu Feb 11 1993 - 04:16:12 CET