Re: Dates in Oracle and ORA-0600

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 03 Sep 1998 20:26:18 GMT
Message-ID: <3612f9cf.113217868@192.86.155.100>

A copy of this was sent to dean_at_gfsmortgage.com (if that email address didn't require changing) On Thu, 03 Sep 1998 19:58:07 GMT, you wrote:

>I am trying to debug an app that regularly causes an ORA-0600 error with no
>numbers in the accompanying []'s.
>
>I can identify the row, but can't access it as a row.
>
>The table stores one varchar2 column and more than a dozen date columns.
>
>The main problem is with the app (I think), but one unusual effect that
>generates no Oracle error message is in one particular column in another row
>that has a date (DD-MM-YY) of 93-01-00.
>
>I didn't believe this so I formatted it to make sure. I tried this:
>
>select to_char(my_date, 'fmDDthsp/Month/YYYY') from boo
>
>TO_CHAR(MY_DATE,'FMDDTHSP/MONTH/YYYY')
>---------------------------------------------------------------------------
>NINETY-THIRD/January/1900
>
>Am I missing something, or does Oracle just not care? I've been trying to
>convince our developers to perform some data integrity from the app, but this
>threw me.

if you use "to_date()" in client programs and insert character strings with dates in them -- Oracle will perform all checks. That is, you should be using:

  insert into T ( c1 ) values ( to_date( :c1, 'dd-mon-yyyy' ) );

If you use OCI or Pro*C *and* you use the internal Oracle format for dates (a 7 byte field) the date field just goes in as 'bits' -- no checking is performed. The documentation states (not in these words) "this is a bad idea - don't use the Oracle internal format, use to_date to convert host strings".

for example, here is a "BAD" pro*c routine that uses the internal Date format:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;

    VARCHAR     dateStr[25];
    char        dateBytes[7];

    EXEC SQL VAR dateBytes is DATE;
EXEC SQL END DECLARE SECTION;     EXEC SQL WHENEVER SQLERROR CONTINUE;     EXEC SQL DROP TABLE TEST_DATE_TBL;     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CREATE TABLE TEST_DATE_TBL ( X DATE );     memset( dateBytes, 0, sizeof(dateBytes) );     dateBytes[2] = 1;

    EXEC SQL INSERT INTO TEST_DATE_TBL VALUES ( :dateBytes );

    EXEC SQL COMMIT WORK;     EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM TEST_DATE_TBL;     EXEC SQL OPEN C1;
    for(;;)
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C1 INTO :dateStr;
        printf( "%.*s\n", dateStr.len, dateStr.arr );

    }
    EXEC SQL CLOSE C1;
}

It prints out 00-000-00, commonly known as garbage (GI - GO)....

If, on the other hand, I used to_date(:dateString), Oracle would have prevented me from inserting any bad dates.

I have seen some 3'rd party applications bypass the to_date() functionality and put bad data into the database. You'll want to see how your developers are getting the dates into the database in their code and, if they are using a tool, how that tool handles dates.

>
>
>Any comments or suggestions would be appreciated.
>
>Thanks,
>-Dean
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

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

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Sep 03 1998 - 15:26:18 CDT